split semicolon delimiter SQL to rows

Question

Just want to ask for help. I'm trying to split delimited values with a semicolon as a delimiter. Comma cannot be replaced to the semicolon since there are values that have comma.

ID   Value
1   | A&B;C;D;E, F

Transform to:

ID   Value
1    A&B
1    C
1    D
1    E, F

I tried tweaking the SQL scripts that i got online but to no success

SELECT F1.ID,
 O.splitdata 
FROM
 (
 SELECT OldID,
 cast('<X>'+replace((SELECT ColumnName + '' FOR XML PATH('')),';','</X><X>')+'</X>' as XML) as xmlfilter from TableName F
 )F1
 CROSS APPLY
 ( 
 SELECT fdata.D.value('.','varchar(max)') as splitdata 
 FROM f1.xmlfilter.nodes('X') as fdata(D)) O

It works for some of my columns but if the columns have special or Illegal characters it outputs this error:

Msg 9411, Level 16, State 1, Line 2
XML parsing: line 1, character 16, semicolon expected

Thanks!

There are a lot of string splitting techniques out there, you don't have to use an XML based function. Read Aaron bertrand's [Split strings the right way – or the next best way](https://sqlperformance.com/2012/07/t-sql-queries/split-strings) and choose a different funcftion. — Zohar Peled, Feb 02 '17 at 10:10
A quick Google for something like `sql split string on delimiter` gives loads of options, including the excellent article linked above by [Zohar Peled](http://stackoverflow.com/users/3094533/zohar-peled) - have you tried any of the easily-Googleable alternative approaches? — 3N1GM4, Feb 02 '17 at 10:12
@mohan111 I just posted an answer which allows such characters in XML-string-splitting. — Shnugo, Feb 02 '17 at 10:49
The linked *possible duplicate* (by Serg) is a great list of possible approaches, but quite old. Many answers there are outdated... I'll close this as duplicate with the hint, to look there for newer answers... — Shnugo, Feb 02 '17 at 10:53

John Cappelletti · Answer 1 · 2017-02-02T12:04:10.420

Option 1 with a UDF

Declare @YourTable table (ID int, Value varchar(max))
Insert Into @YourTable values
(1,'A&B;C;D;E, F')

Select A.ID
      ,B.*
 From @YourTable A
 Cross Apply [dbo].[udf-Str-Parse-8K](A.Value,';') B

Option 2 without a UDF

Select A.ID
      ,B.*
 From @YourTable A
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(A.Value,';','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
              ) B

Both Return

ID  RetSeq  RetVal
1   1       A&B
1   2       C
1   3       D
1   4       E, F

This UDF is XML Safe and VERY fast

CREATE FUNCTION [dbo].[udf-Str-Parse-8K] (@String varchar(max),@Delimiter varchar(25))
Returns Table 
As
Return (  
    with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
           cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
           cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)

    Select RetSeq = Row_Number() over (Order By A.N)
          ,RetVal = LTrim(RTrim(Substring(@String, A.N, A.L)))
    From   cte4 A
);
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Much faster than str-Parse, but limited to 8K
--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')

score 1 · Accepted Answer · answered Feb 02 '17 at 10:31

1

If you do not like a function, or if you do not have the rights to create a new function, you can use the quite fast XML approach. In your case it needs some extra effort to get this XML-safe (due to special characters and the ; as delimiter):

Declare @Dummy table (ID int, SomeTextToSplit varchar(max))
Insert Into @Dummy values
 (1,'A&B;C;D;E, F')
,(2,'"C" & "D";<C>;D;E, F');

DECLARE @Delimiter VARCHAR(10)=';';
WITH Casted AS
(
    SELECT *
          ,CAST('<x>' + REPLACE((SELECT REPLACE(SomeTextToSplit,@Delimiter,'§§Split$me$here§§') AS [*] FOR XML PATH('')),'§§Split$me$here§§','</x><x>') + '</x>' AS XML) AS SplitMe
    FROM @Dummy
)
SELECT Casted.*
      ,x.value('.','nvarchar(max)') AS Part 
FROM Casted
CROSS APPLY SplitMe.nodes('/x') AS A(x)

The result

1   A&B
1   C
1   D
1   E, F
2   "C" & "D"
2   <C>
2   D
2   E, F

answered Feb 02 '17 at 10:31

Shnugo

66,100
9
53
114

Very nice - migrated into my XML parse function – John Cappelletti Feb 02 '17 at 10:46
Thanks for this one! it actually worked. Only note that the NULL values are turned to blanks. but nonetheless, it's working perfectly. It ran for 28 secs compared to my Cursor Script which ran for 10 mins. – Elle Gams Feb 22 '17 at 08:18
@MirelleGameng, not quite true... If your string contains two *semi-colons* one after the other (`SomeValue;;More`) there is no character between these two. This is not read as a blank (=`space`), but as an *empty string* (=*zero-length-string*). You can use `NULLIF()` to translate this into `NULL` if needed. If this works, please accept the answer, thx! – Shnugo Feb 22 '17 at 08:21
Already accepted. Thanks again @Shnugo :) – Elle Gams Feb 22 '17 at 08:25

Ronak Patel · Answer 3 · 2017-02-02T10:14:02.540

Please use the function below to split a string by a specific delimiter:

CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))        
returns @temptable TABLE (SplitValue varchar(8000))        
as        
begin        
    declare @idx int        
    declare @slice varchar(8000)        

    select @idx = 1        
       if len(@String)<1 or @String is null  return        

    while @idx!= 0        
    begin        
        set @idx = charindex(@Delimiter,@String)        
        if @idx!=0        
            set @slice = left(@String,@idx - 1)        
        else        
            set @slice = @String        

        if(len(@slice)>0)   
            insert into @temptable(SplitValue) values(@slice)        

        set @String = right(@String,len(@String) - @idx)        
        if len(@String) = 0 break        
    end    
return       
end

Let me know if you have any queries.

Thanks .

Approaches with loops are out-dated... – Shnugo Feb 02 '17 at 10:23 — Shnugo, Feb 02 '17 at 10:23

split semicolon delimiter SQL to rows

3 Answers3

Linked