SQL Server Performance

Split Data in string

Discussion in 'SQL Server 2008 General Developer Questions' started by kdineshbabuct, Jul 30, 2009.

  1. kdineshbabuct New Member

    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
  2. Madhivanan Moderator

    Search for Split +SQL Server in google
  3. Madhivanan Moderator

    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
  4. kdineshbabuct New Member

  5. davidfarr Member

    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"'

Share This Page