SQL command to split by "/"

Question

I have a data column with values like this:

table: type

ID|Descriptions  
1 |chair/table/plates/  
2 |chair2/table2/plates2/

What will be the SQL command to split it by "/" ?

Expected output

ID|Description1|Description2|Description3|  
1 |chair       |table       |plates  
2 |chair2      |table2      |plates2

will this description have only 3 `/`? – Nisha Sep 27 '13 at 03:56 — Nisha, Sep 27 '13 at 03:56
more than 3 there are cases 3 or 4 – user2617053 Sep 27 '13 at 08:46 — user2617053, Sep 27 '13 at 08:46

score 1 · Accepted Answer · answered Sep 27 '13 at 04:03

Try this

;WITH Split_Descr ([ID],[Descriptions], xmldescr)
AS
(
    SELECT [ID],
    [Descriptions],
    CONVERT(XML,'<Descr><desc>'  
    + REPLACE([Descriptions],'/', '</desc><desc>') + '</desc></Descr>') AS xmldescr
      FROM Table1
)

 SELECT [ID],      
 xmldescr.value('/Descr[1]/desc[1]','varchar(100)') AS Descr1,    
 xmldescr.value('/Descr[1]/desc[2]','varchar(100)') AS Descr2,
 xmldescr.value('/Descr[1]/desc[3]','varchar(100)') AS Descr3
 FROM Split_Descr

SQL FIDDLE DEMO

SQL command to split by "/"

1 Answers1