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 tags
select data from @temp where id%2=0

--Extract the data available outside the tags
select 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)>0
begin
        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_no
from
(
        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 t
where 
       
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

Also refer these
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/10/12/removing-tags-from-a-string.aspx
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/08/03/regular-expression.aspx

Published Wednesday, November 18, 2009 9:03 AM by Madhivanan

Comments

No Comments