Regular expression

One of the posters asked a question in the forums about finding a number in an expression consists of alphabets and number if a particular digit is provided

Consider the following set of data

this is test place located at no 123456 test
foo 35234
bar 126831
72348707 foo
foo 987654 bar


If the digit provided is 7, then the numbers 72348707 and 987654 should be extracted from the data

One of the methods is using a regular expression

declare @sample table
(
        data varchar(200)
)

insert @sample

select 'this is test place located at no 123456 test' union all
select 'foo 35234' union all
select 'bar 126831' union all
select '72348707 foo ' union all
select
'foo 987654 bar'

declare @digits int

set @digits = 7

 

select
    data
,@digits as expression,
    right(substring(' '+data+' ', 1,pos),charindex(' ',reverse(substring(' '+data+' ', 1,pos))))+
    substring
    (
        substring(' '+data+' ', pos+1,len(' '+data+' ')),1,charindex(' ',substring(' '+data+' ', pos+1,len(' '+data+' ')))
    ) 
    as number
from
(
    select 
        data
,
        patindex('%' + cast(@digits as varchar(10)) + '%', data) as pos 
    from 
        @sample
    where 
        data
like '%'+CAST(@digits as varchar(10))+'%'
) as d

Published Monday, August 3, 2009 10:53 AM by Madhivanan

Comments

# Regular expression

Monday, August 3, 2009 6:16 PM by SqlServerKudos

Kudos for a great Sql Server article - Trackback from SqlServerKudos

# Parsing a string

Wednesday, November 18, 2009 9:19 AM by Madhivanan

Now-a-days lot of questions are aked in the forum about extracting particular text from a string Here

# Parsing a string

Wednesday, November 18, 2009 9:26 AM by SQL Server Transact-SQL (SSQA.net)

Now-a-days lot of questions are aked in the forum about extracting particular text from a string Here