Extracting EMail and Phone numbers from the long String

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 321
Here, dbo.Tally table is for looping through the string values and #ComplanyInformation temp table is having sample data..

--Create a tally table.  All Hail Jeff Moden!
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.
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 '%@%.%'

/* Email of each user:
Id Emails
1 santosh@gmail.com
2 jr68222@gmail.com
*/

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

/* Phone Numbers:
Id PhoneNumbers
1 9902007445
1 9765555512
*/
drop table #CompanyInformation;

Comments or Responses

Posted by: vishalneeraj-24503 on: 7/21/2014 Level:Platinum | Status: [Member] [MVP] | Points: 10
Hi Bandi,

What is the use of above script?

Login to post response