how to find phone number from content + sqlserver

Posted by Santosh4u under Sql Server on 11/26/2013 | Points: 10 | Views : 1294 | Status : [Member] | Replies : 11
Hi

how do i find the phone numbers from a string in sqlserver.

i have a table have columns id,Content

id is auto generated and content column contains different CV's content and i need to find all the phone numbers and email from this CV content.

in C# i have done the same using regexp but not able to do in sqlserver..is it possible to use regexp in sqlserver..

Ex: Input : Content = 'santosh kumar this saaksa santosh@gmail.com 9902007445 testing tet 9765555512 fdfd 43434343 '

output should be :
Phone Numbers: 9902007445 ,9765555512
Email: santosh.mca15@gmail.com


Thanks
Santosh




Responses

Posted by: vishalneeraj-24503 on: 11/26/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Please find below code:
string a = "str123";
string b = string.Empty;
int val;
for (int i=0; i< a.Length; i++)
{
if (Char.IsDigit(a[i]))
b += a[i];
}
if (b.Length>0)
val = int.Parse(b);

//Also try this:
Dim mytext As String = "123a123"
Dim myChars() As Char = mytext.ToCharArray()
For Each ch As Char In myChars
If Char.IsDigit(ch) Then
MessageBox.Show(ch)
End If
Next

or:

Private Shared Function Num(ByVal value As String) As Integer
Dim returnVal As String = String.Empty
Dim collection As MatchCollection = Regex.Matches(value, "\d+")
For Each m As Match In collection
returnVal += m.ToString()
Next
Return Convert.ToInt32(returnVal)
End Function

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Santosh4u on: 11/26/2013 [Member] Bronze | Points: 25

Up
0
Down
Hi Vishal,
i think u are not clear of my question.please try to under stand that i need to find the phone numbers from text that to in sqlserver.

Ex: Input : Content = 'santosh kumar this saaksa santosh@gmail.com 9902007445 testing tet 9765555512 fdfd 43434343 '

output should be : 9902007445 ,9765555512

Thanks
Santosh

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 11/26/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Santosh4u,

Where you want to decode. In C# or SQL?

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/26/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
if he format of cv_content is fixed then we can

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Santosh4u on: 11/26/2013 [Member] Bronze | Points: 25

Up
0
Down
Hi Allemahesh,
i want it in sqlserver.

Thanks

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Santosh4u on: 11/26/2013 [Member] Bronze | Points: 25

Up
0
Down
Hi Bandi
format is same means?
is it possible to get this output using regexp in sqlservr.

Thanks
Santosh

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/26/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
>>format is same means?
Name of candidate mailID something something Phone number

if all of the cv_content data follows the above format name followed by mailId and then followed by phone number then we can able to get the required data

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 11/27/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Santosh4u,

This is very simple in SQL.
Just run the below statement and you will get the required output.

DECLARE @CONTENT as varchar(max) = 'santosh kumar this saaksa santosh@gmail.com 9902007445 testing tet 9765555512 fdfd 43434343 ' 

-- This will give you all the mobile numbers
SELECT * FROM dbo.Split(@CONTENT, ' ') WHERE Data NOT LIKE '%[^0-9]%' AND LEN(Data) > 9
-- This will give you all the email address
SELECT * FROM dbo.Split(@CONTENT, ' ') WHERE Data LIKE '%_@__%.__%'


Here I am using the Split() function and here the Split() function code:-


CREATE FUNCTION [dbo].[Split]
(
@RowData varchar(MAX),
@SplitOn varchar(5)
)
RETURNS @RtnValue TABLE
(
Id INT IDENTITY(1,1) NOT NULL,
Data varchar(MAX)
)
AS
BEGIN
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (Data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
End
Insert Into @RtnValue (Data)
Select Data = ltrim(rtrim(@RowData))
Return
END


Out Put:-

Id	Data
6 9902007445
9 9765555512

Id Data
5 santosh@gmail.com


Please let me know if you have other any issue.


Happy Coding,
If it helps you or directs U towards the solution, MARK IT AS ANSWER

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/27/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down


--Create a tally table.
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally

SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2

ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC

--Now use the tally table to extract the relevant information.
--Here you should place your table
create table #CompanyInformation
(
Id int,
String varchar(1000)
);

insert into #CompanyInformation values (1, 'santosh kumar this saaksa santosh@gmail.com 9902007445 testing tet 9765555512 fdfd 43434343 ');
insert into #CompanyInformation values (2, 'DEF Company jr68222@gmail.com 123 Main St Floor 4');


SELECT
Id
,SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1) Emails
FROM
dbo.Tally t
cross join #CompanyInformation p
WHERE
N < LEN(' ' + p.String + ' ')
AND SUBSTRING(' ' + p.String + ' ', N, 1) = ' '
AND SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1) LIKE '%@%.%'


SELECT
Id
,SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1) PhoneNumbers
FROM
dbo.Tally t
cross join #CompanyInformation p
WHERE
N < LEN(' ' + p.String + ' ')
AND SUBSTRING(' ' + p.String + ' ', N, 1) = ' '
AND SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1) NOT LIKE '%[^0-9]%' AND LEN(SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1)) > 9

drop table #CompanyInformation;


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Santosh4u on: 12/2/2013 [Member] Bronze | Points: 25

Up
0
Down
Hi Allemahesh,
how to i call this function in select statment..
something like below
select id,content,dbo.Split(content, ' ') from tblContent WHERE Data LIKE '%_@__%.__%'
actually i want to update the phonenumbes field as the passing content value for all the table data.

Thanks
Santosh


Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 12/2/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
CREATE TABLE CONTENT(id int, content varchar(max))
insert CONTENT values( 1, 'santosh kumar this saaksa santosh@gmail.com 9902007445 testing tet 9765555512 fdfd 43434343 ' ), (2, 'santosh kumar this saaksa santosh@yahoo.com ')

-- This will give you all the mobile numbers
SELECT * FROM CONTENT
cross APPLY dbo.Split(CONTENT, ' ')
WHERE Data NOT LIKE '%[^0-9]%' AND LEN(Data) > 9
-- This will give you all the email address
SELECT * FROM CONTENT
cross APPLY dbo.Split(CONTENT, ' ')
WHERE Data LIKE '%_@__%.__%'


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response