Splitting delimited strings in SQL

This is an issue I come back to a couple of times a year and always forget how I did it last so here’s the info…

I recently found a great blog that compares all the different methods of splitting delimited strings in SQL Server (there are lots!), interestingly it isn’t always the same solution that is the fastest – It depends on the number of elements in your strings…

http://www.sqlservercentral.com/articles/Tally+Table/72993/

As I’m never expecting more than 30 elements in my strings I’ve settled on the XML method – I found a good example of it here http://www.sqlservercentral.com/articles/XML/66932/

However this wasn’t working for me as I have special characters in my strings such as * and / which are illegal characters in XML – To get around this I had a chat with our DBA and he suggested adding "<![CDATA[" to act as effectively a text qualifier. This works great, see the code below…

Apologies for the dodgy apostrophes in the code below – I’m working on it!

Since playing with this a bit I ran into a problem with strange ASCII characters in my strings which was causing it to fail – The trick was to find out what the character was (in my case it was a ‘record separator’) and the remove it using REPLACE and CHAR() – Mine was a CHAR(30).

CREATE FUNCTION [dbo].[ufnSplitXml]  
   (@List nvarchar(max), @Delim char(1)= ',')  
RETURNS @Values TABLE (Item nvarchar(100))AS  
  
--  This function accepts a parameter @List containing the string to be split eg 'abc,def,ghi'
--  The default delimiter is comma but you can specify others if required.
--  Use a cross apply to join to this table valued function.
  
  BEGIN 
  
    DECLARE @XML XML
    
    IF Right(@List,LEN(@Delim)) = @Delim
    BEGIN
          SET @List = LEFT(@List,LEN(@List) - Len(@Delim))
    END

    SET @xml = N'<t><![CDATA[' + REPLACE(@List,@Delim,']]></t><t><![CDATA[') + ']]></t>'  

    INSERT INTO @Values
    SELECT  r.value('.','varchar(MAX)') as Item  
    FROM  @xml.nodes('/t') as records(r)
 
  RETURN  
  END