none
How to split and return all strings between 2 delimiters ?

    Question

  • For example, I have records like this:

    ID Text

    1 <img alt="" src="a.gif">Content1<img alt="" src="b.gif">Content2<img src="c.gif">Content

    2 <img name="" src="q.gif">ContentX<img src="w.gif">contentY

    And I need a function to find strings start with '<img' and end with '>', the result looks like this:

    ID Text

    1 <img alt="" src="a.gif">

    1 <img alt="" src="b.gif">

    1 <img src="c.gif">

    2 <img name="" src="q.gif">

    2 <img src="w.gif">
    Can anyone help me ?


    • Edited by D.MSK Friday, October 18, 2013 6:58 AM
    Friday, October 18, 2013 6:54 AM

Answers

  • create table test(id int,col varchar(1000))
    insert into test values (1,'<img alt="" src="a.gif">Content1<img alt="" src="b.gif">Content2<img src="c.gif">Content')
    ,(2,'<img name="" src="q.gif">ContentX<img src="w.gif">contentY')
     
    SELECT id, Replace(Replace(S.a.value('.', 'VARCHAR(100)'),'_','<'),'|','>') AS splitVal
    FROM
    (
    SELECT *,CAST ( N'<H>' + Replace(REPLACE(col, '<img', '<r>_img'),'gif">','gif"|</r>') + '</H>' AS XML) AS [vals]
    FROM test) d
    CROSS APPLY d.[vals].nodes('/H/r') S(a)
    drop table test

    Friday, October 18, 2013 3:04 PM
    Moderator

All replies

  • Try recursive CTE with string manipulation.

    create table test1(c1 int,c2 varchar(1000))
    insert into test1 values (1,'<img alt="" src="a.gif">Content1<img alt="" src="b.gif">Content2<img src="c.gif">Content')
    ,(2,'<img name="" src="q.gif">ContentX<img src="w.gif">contentY')
    
    ;with cte
    as 
    (
    select c1, cast( substring( c2,charindex('<img',c2) ,charindex('">',c2)+1) as varchar(1000)) as  [delimt]
    ,cast(stuff(c2,1,charindex('">',c2)+1,'') as varchar(1000)) as  [c2temp]  from test1 
    union all 
    select c1,cast(substring( [c2temp],charindex('<img',[c2temp]),charindex('">',[c2temp])-charindex('<img',[c2temp])+2)  as varchar(1000))
    ,cast(stuff([c2temp],1,charindex('">',[c2temp])+1,'')  as varchar(1000))  from cte 
    where charindex('<img',[c2temp])>1
    )
    select c1,delimt from cte order by c1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, October 18, 2013 7:28 AM
  • One way is to use Regular Expressions. Its flexible and very fast.

    For more info see these links:

    Regular Expressions Make Pattern Matching And Data Extraction Easier

    Regular Expressions in MS SQL Server 2005/2008

    regular-expressions.info


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Friday, October 18, 2013 7:45 AM
  • create table test(id int,col varchar(1000))
    insert into test values (1,'<img alt="" src="a.gif">Content1<img alt="" src="b.gif">Content2<img src="c.gif">Content')
    ,(2,'<img name="" src="q.gif">ContentX<img src="w.gif">contentY')
     
    SELECT id, Replace(Replace(S.a.value('.', 'VARCHAR(100)'),'_','<'),'|','>') AS splitVal
    FROM
    (
    SELECT *,CAST ( N'<H>' + Replace(REPLACE(col, '<img', '<r>_img'),'gif">','gif"|</r>') + '</H>' AS XML) AS [vals]
    FROM test) d
    CROSS APPLY d.[vals].nodes('/H/r') S(a)
    drop table test

    Friday, October 18, 2013 3:04 PM
    Moderator