Getting Data out of Email using substring in SQL

Question

i have the body of the email as a column, the email has a phone numbers in it. i need to get the phone numbers out.

Sample data,

Col1                                     Col2     Col3

ssdfncs 4355543543 sdfsfdsjg 3254546342 esrdgdfggfdg 3455453453 active     1

4534645623 sdfsdfds 4365645623 sfsdgdryregrehg 3545664664       disable    0

O/P

Col1    Col2  Col3

4355543543 active    1

3254546342 active    1

3455453453 active    1

4534645623 disable   0

4365645623 disable   0

3545664664 disable   0 

Answer 1

One possible option is to extract only numbers from that col.

Import all the col as it is to a stage table.

Then filterout only numbers for this col and rest of the cols to main table.

Following SQL User Defined Function will extract/parse numbers from the string.
 CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))
 RETURNS VARCHAR(1000)
 AS
 BEGIN
 DECLARE @Count INT
 DECLARE @IntNumbers VARCHAR(1000)
 SET @Count = 0
 SET @IntNumbers = ''
 
WHILE @Count <= LEN(@String)
 BEGIN
 IF SUBSTRING(@String,@Count,1) >= '0'
 AND SUBSTRING(@String,@Count,1) <= '9'
 BEGIN
 SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
 END
 SET @Count = @Count + 1
 END
 

RETURN @IntNumbers
 END
 GO
 
Run following script in query analyzer.
 SELECT dbo.ExtractInteger('sdfssdafafa53454354354353')
 GO

---

Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Answer 2

Also one more method.

Declare @s varchar(100),@result varchar(100)
set @s='ksdjhfksdjfh3894239847329874' 
set @result=''

select
    @result=@result+
            case when number like '[0-9]' then number else '' end from 
    (
         select substring(@s,number,1) as number from 
        (
            select number from master..spt_values 
            where type='p' and number between 1 and len(@s)
        ) as t
    ) as t 
select @result as only_numbers 

---

Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Answer 3

Actually my filter is a 5 by 5 code like ADF34-DSDF2-43543-SDCSD-34552.

I need to filter out this 5 by 5 code from my data.

i got the code to get the first one.

substring(Body, (PATINDEX('%[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][-][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][-][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][-][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][-][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]%', Body)),29)

i don't know how to get the rest of them