Search This Blog

SQL 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 numberfrom (    select
        data,        patindex('%' + cast(@digits as varchar(10)) + '%', data) as pos     from
        @sample    where
        data like '%'+CAST(@digits as varchar(10))+'%') as d 

Removing tags from a string

In http://ask.sqlteam.com, a question was asked about removing the comments from the HTML template
The comments have the following pattern
{/*}comment{*/}
The comments can be nested too
The task is to remove all the comments
Here is my solution
declare @string varchar(8000) ,@data varchar(8000)
select @string = 'AaaA{/*}XxxX{/*}YyyyY{*/}ZzzzZ{*/}BbbB{/*}CCC{*/}DdD'
select @string=replace(replace(@string, '{/*}','~{/*}'),'{*/}','~{*/}')
declare @t table(id int identity(1,1),data varchar(8000))
insert into @t(data)
select         substring(@string, n, charindex('~', @string + '~', n ) - n)from (         select number as n from master..spt_values where type='p' ) numbers where         substring( '~' + @string, n, 1 ) = '~'

--Method 1
set @string='' select @string =@string+data from (    select data from @t where id=1     union all
    select substring(data,5,len(data)) from @t     where (data like '{*/}%' and id%2=1)
) as t
select @string
--Method 2
select @string =''
select
        @string =@string +substring(t2.data,5,len(t2.data)) from
        @t as t1 inner join @t as t2 on t1.id+1=t2.idwhere
        t2.data like '{*/}%' and t2.id%2=1 and t2.data<>'{*/}'
select @string

Parsing a string

Now-a-days lot of questions are asked in the forum about extracting particular text from a string
Here I give some examples on how to do it
--Example 1 (Extracting tags and other informations)
declare @s varchar(100)
declare @temp table(id int identity(1,1),data varchar(100))
set @s='567<a>data<fg>ua<li>test<iu>welcome'
select @s=replace(replace(@s,'<','~!@#'),'>','~!@#')
while charindex('~!@#',@s)>0   begin
        insert into @temp(data)        select substring(@s,1,charindex('~!@#',@s)-1)        set @s=substring(@s,charindex('~!@#',@s)+4,len(@s))end
insert into @temp(data)select @s

--Extract the tagsselect data from @temp where id%2=0
--Extract the data available outside the tagsselect data from @temp where id%2=1
--Example 2 (Extract URLs)
declare @s varchar(1000)
declare @temp table(id int identity(1,1),data varchar(1000))
set @s='"ramkumar<a href="www.yahoo.com" mce_href="www.yahoo.com">hhh</a>mahesh<a title href="www.gmail.com" mce_href="www.gmail.com" />hhjjkjhsdf< as href="test.test.com" mce_href="test.test.com"'
while charindex('href=',@s)>0begin
        insert into @temp(data)        select substring(@s,charindex('href=',@s)+6,charindex('.com',@s)-charindex('href=',@s)-2)        set @s=substring(@s,charindex(substring(@s,charindex('href=',@s)+6,charindex('.com',@s)-charindex('href=',@s)-               2),@s)+len(substring(@s,charindex('href=',@s)+6,charindex('.com',@s)-charindex('href=',@s)-2)),len(@s))end select data from @temp
--Example 3 (Extract phone numbers with the format ddd-ddd-dddd where d denotes a number)
select
        data,        substring(data,patindex('%[0-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]%',data),12) as ph_nofrom(        select 'kashdk hasfjh sjkfhk h123-456-8765 kjsdkfjj h093 kljsdf903354512' as data union all        select 'khasd ksfjh 345-245-9871kljk 90234 99jkxc 902304809n kljsdkfj9034' ) as twhere
        patindex('%[0-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]%',data)>0
--Example 4 (Extract amount starts with $)
select fruit,substring(amount,1,patindex('%[0-9][ ]%',amount+' ')) from(select
        Fruit,         substring(fruit,charindex('$',fruit),len(fruit)) as amount        from         (                select '9 Lemons cost 67 $99.99 on sale' as fruit union all                select '$5.99 Apples 877 are on sale' union all                select 'Where are the $65.99 lemons 7856' union all                select 'Oranges costs $99.5'        )as Pricing) as t

SQL Server Script out Procedures to seperate files

In this post script-out-procedures-and-functions-part-2, I showed how to script out the procedures in a single file
Here is another way to do the same but different file for each procedure. The file name will be the name of the procedure.
declare @sps table(proc_name varchar(100),texts varchar(8000))
insert into @spsselect name,'sp_helptext '''+name+'''' from sysobjects where xtype in ('p','fn')
create table scripts(sps varchar(8000))
declare @texts varchar(1000)declare @file_name varchar(100)declare @sql varchar(1000)
select @texts=min(texts)from @sps
while @texts>''Begin
        select @file_name=proc_name from @sps where texts=@texts        EXEC('insert into scripts(sps) EXEC '+ @texts) insert into scripts(sps) select 'GO'        select @texts=min(texts)from @sps where texts>@texts         set @sql='bcp "select * from yourdb..scripts" queryout "c:\'+@file_name+'.txt" -c'         EXEC master..xp_cmdshell @sql        truncate table scriptsEnd
drop table scripts