Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Parsing a string

Nov 18 2009 9:03AM by Madhivanan   

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='567dataua
  • testwelcome
  • ' 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</li></a>hhhmahesh<a href="/www.gmail.com"></a>hhjjkjhsdf&lt; 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 this
    http://beyondrelational.com/blogs/madhivanan/archive/2009/10/12/removing-tags-from-a-string.aspx
    

    Tags: t-sql, sql_server, parsing,


    Madhivanan
    3 · 40% · 12924
    1
     
    0
    Lifesaver
     
    0
    Refreshed
     
    0
    Learned
     
    0
    Incorrect



    Submit

    Your Comment


    Sign Up or Login to post a comment.

    "Parsing a string" rated 5 out of 5 by 1 readers
    Parsing a string , 5.0 out of 5 based on 1 ratings
        Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]