I need to split the data in a string. I have one string which contain urls. i need to split those url's alone. Ex: "ramkumar<a href="www.yahoo.com">hhh</a>mahesh<a title href="www.gmail.com" />hhjj" Expected output www.yahoo.com www.gmail.com is there any regular expression or split methods to solve this. Thanks in advance Regards Dinesh Babu.K
Ok. There must be some other smarter way than thisdeclare @s varchar(100)declare @temp table(id int identity(1,1),val varchar(100))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" />hhjj"' while charindex('href=',@s)>0begin insert into @temp(val) 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))endselect val from @temp
Thanks for your valuable reply. It's working fine but i am facing one issue. I am not always having .com.It may .aspx,etc Ex: Real time data"<strong> <a href="http://1965.iii.yahoo.com/Fppirm%20Info/yahoo/93952.aspx">hild Support and S<a href="http://1965.ya.google.com/Firm%20Info/huuu/93952.aspx">" Expected output http://1965.iii.yahoo.com/Firm Info/yahoo/93952.aspx http://1965.ya.google.com/Firm Info/huuu/93952.aspx How can solve this issue. Regards Dinesh Babu.K
The previous posts were all on the right track, it was just a matter of proper pattern recognition. All elements of the array have href= and " in common. The logical way to isolate the URLs is therefore to identify and separate based on that, as follows: create procedure dbo.spSplitURL @String varchar(8000) as declare @temp table (id int identity(1,1),val varchar(1000)) declare @slice varchar(1000), @idx int, @idx2 int set @idx = 1 if len(@String)<1 or @String is null set @idx = 0 while @idx <> 0 begin set @idx = charindex('href=',@String) set @idx2 = charindex('"',@String,@idx+6) if @idx <> 0 and @idx2 <> 0 set @slice = substring(@String,@idx+6,@idx2-@idx-6) else set @slice = @String if @idx <> 0 and @idx2 <> 0 insert @temp (val) values (@slice) set @String = right(@String,len(@String) - @idx2) end select val from @temp go Use as follows: exec spSplitURL '<a href="http://1965.iii.yahoo.com/Fppirm%20Info/yahoo/93952.aspx">hild Support and S<a href="http://1965.ya.google.com/Firm%20Info/huuu/93952.aspx">' exec spSplitURL '"ramkumar<a href="www.yahoo.com">hhh</a>mahesh<a title href="www.gmail.com" />hhjj"'