Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 3281
Home > Forums > Sql Server >

how to find phone number from content + sqlserver

Posted by Santosh4u under Sql Server on 11/26/2013 | Points: 10 | Views : 2141 | Status : [Member] | Replies : 11
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
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



Reply | Reply with Attachment
Alert Moderator 

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

Latest Posts