Split function in SQL Server

Question

I have two tables:

Tabletemp:

Itemcode   Barcode 
---------------------------------------
100001     896633212,586231478
100002     639933212/236232369
100003     930933212 987232369 633214589
100004     940933212

and I want to insert the data into another table Tablebarcode as following

 ItemCode   Barcode
 ---------------------
 100001     896633212
 100001     586231478
 100002     639933212
 100002     236232369
 100003     930933212 
 100003     987232369 
 100003     633214589
 100004     940933212

There are mutiple questions asked for the same ,one of that(https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns?noredirect=1&lq=1) — TheGameiswar, Sep 19 '17 at 03:43
But here iam having / or comma or space i have to split three scenarios.. — Karthick Saravana, Sep 19 '17 at 04:20

score 2 · Answer 1 · answered Sep 19 '17 at 04:34

CROSS APPLY the split function several times and use different delimiter

select  *
from    Tabletemp t
        cross apply DelimitedSplit8K(t.Barcode, ',') a
        cross apply DelimitedSplit8K(a.Item, '/') b
        cross apply DelimitedSplit8K(b.Item, ' ') c

Here i am using the DelimitedSplit8K by Jeff Moden http://www.sqlservercentral.com/articles/Tally+Table/72993/

score 1 · Answer 2 · answered Sep 19 '17 at 03:58

1

First, get yourself a string spltter See this article by Jeff Moden The function(s) it builds allow you to pass in a delimited string, and split each value on to its own row.so you simply

select tt.ItemCode, x.ItemValue as BarCode
from TableTemp tt
cross apply SplitString_CRL(BarCode, ' ')

That will split each overloaded bar code into a single row per.

answered Sep 19 '17 at 03:58

Xedni

3,662
2
16
27

Thanks for reply, But Iam having / or comma or space – Karthick Saravana Sep 19 '17 at 04:19

score 0 · Answer 3 · answered Sep 19 '17 at 11:06

Converting into Xml and Split we can get the result

SELECT Itemcode, 
       split.a.value('.', 'nvarchar(1000)') AS Barcode 
FROM   (SELECT Itemcode, 
               Cast('<S>' 
                    + Replace(Replace(Replace(Barcode, '/', ','), ' ', ','), ',' 
                    , 
                    '</S><S>') 
                    + '</S>' AS XML) AS Barcode 
        FROM   #tabletemp) AS A 
       CROSS apply barcode.nodes('S') AS Split(a)

Result

 ItemCode   Barcode
 ---------------------
 100001     896633212
 100001     586231478
 100002     639933212
 100002     236232369
 100003     930933212 
 100003     987232369 
 100003     633214589
 100004     940933212

Split function in SQL Server

3 Answers3