Extract Phone numbers and Email IDs from string

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 547
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 ')


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

GO
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
GO
-- 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 '%_@__%.__%'

DROP TABLE CONTENT
DROP FUNCTION dbo.Split

Comments or Responses

Login to post response