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='567datauatestwelcome'
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< 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