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