I have a SQL Server 2008 R2 column containing a string which I need to split by a comma. I have seen many answers on StackOverflow but none of them works in R2. I have made sure I have select permissions on any split function examples. Any help greatly appreciated.

7 upvote
  flag
This is one of the million answers that I like //allinonescript.com/a/1846561/227755 – nurettin
2 upvote
  flag
What do you mean "none of them work"? Can you be more specific? – Aaron Bertrand
upvote
  flag
Andy did point me in the right direction as I was executing the function incorrectly. This is why none of the other stack answers worked. My fault. – Lee Grindon
2 upvote
  flag
possible duplicate of Split string in SQL – Luv
upvote
  flag
There's a mdq.RegexSplit function in the "Master Data Services" add-on, which may help. Certainly worth investigating. – jpaugh

21 Answers 11

up vote 130 down vote accepted

I've used this SQL before which may work for you:-

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

and to use it:-

SELECT * FROM dbo.splitstring('91,12,65,78,56,789')
1 upvote
  flag
Nice one, this is exactly what I was looking for thanks very much – Lee Grindon
1 upvote
  flag
Thanks a lot Andy. I made a small enhancement to your script to allow the function to return an item at a specific index in the split string. It is useful only in situations when you the structure of the column one is parsing. gist.github.com/klimaye/8147193 – CF_Maintainer
1 upvote
  flag
I posted some improvements (with backing test cases) to my github page here. I will post it as an answer in this Stack Overflow thread when I have enough rep to exceed post "protection" – mpag
upvote
  flag
Great answer. Also works on earlier versions of SQL Server, such as 2005, which one of my clients is still using. – Bart Read
upvote
  flag
thanks. It saves my 12 hours. – Hafiz Shehbaz Ali
3 upvote
  flag
Although this is a great answer, it is outdated... Procedural approaches (especially loops) are something to avoid... It's worth to look into newer answers... – Shnugo
upvote
  flag
This doesn't work for select * from dbo.splitstring('') – Pasi Savolainen

if you replace

WHILE CHARINDEX(',', @stringToSplit) > 0

with

WHILE LEN(@stringToSplit) > 0

you can eliminate that last insert after the while loop!

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE LEN(@stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)


if @pos = 0
        SELECT @pos = LEN(@stringToSplit)


  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 RETURN
END
upvote
  flag
This would result in the last character of the last element being truncated. i.e. "AL,AL" would become "AL" | "A" i.e. "ABC,ABC,ABC" would become "ABC" | "ABC" | "AB" – Terry
upvote
  flag
appending +1 to SELECT @pos = LEN(@stringToSplit) appears to address that issue. However, the SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos) will return Invalid length parameter passed to the LEFT or SUBSTRING function unless you add +1 to the third parameter of SUBSTRING as well. or you could replace that assignment with SET @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, 4000) --MAX len of nvarchar is 4000 – mpag
1 upvote
  flag
I posted some improvements (with backing test cases) to my github page here. I will post it as an answer in this Stack Overflow thread when I have enough rep to exceed post "protection" – mpag

I had to write something like this recently. Here's the solution I came up with. It's generalized for any delimiter string and I think it would perform slightly better:

CREATE FUNCTION [dbo].[SplitString] 
    ( @string nvarchar(4000)
    , @delim nvarchar(100) )
RETURNS
    @result TABLE 
        ( [Value] nvarchar(4000) NOT NULL
        , [Index] int NOT NULL )
AS
BEGIN
    DECLARE @str nvarchar(4000)
          , @pos int 
          , @prv int = 1

    SELECT @pos = CHARINDEX(@delim, @string)
    WHILE @pos > 0
    BEGIN
        SELECT @str = SUBSTRING(@string, @prv, @pos - @prv)
        INSERT INTO @result SELECT @str, @prv

        SELECT @prv = @pos + LEN(@delim)
             , @pos = CHARINDEX(@delim, @string, @pos + 1)
    END

    INSERT INTO @result SELECT SUBSTRING(@string, @prv, 4000), @prv
    RETURN
END

A solution using a CTE, if anyone should need that (apart from me, who obviously did, that is why I wrote it).

declare @StringToSplit varchar(100) = 'Test1,Test2,Test3';
declare @SplitChar varchar(10) = ',';

with StringToSplit as (
  select 
      ltrim( rtrim( substring( @StringToSplit, 1, charindex( @SplitChar, @StringToSplit ) - 1 ) ) ) Head
    , substring( @StringToSplit, charindex( @SplitChar, @StringToSplit ) + 1, len( @StringToSplit ) ) Tail

  union all

  select
      ltrim( rtrim( substring( Tail, 1, charindex( @SplitChar, Tail ) - 1 ) ) ) Head
    , substring( Tail, charindex( @SplitChar, Tail ) + 1, len( Tail ) ) Tail
  from StringToSplit
  where charindex( @SplitChar, Tail ) > 0

  union all

  select
      ltrim( rtrim( Tail ) ) Head
    , '' Tail
  from StringToSplit
  where charindex( @SplitChar, Tail ) = 0
    and len( Tail ) > 0
)
select Head from StringToSplit
ALTER FUNCTION [dbo].func_split_string
(
    @input as varchar(max),
    @delimiter as varchar(10) = ";"

)
RETURNS @result TABLE
(
    id smallint identity(1,1),
    csv_value varchar(max) not null
)
AS
BEGIN
    DECLARE @pos AS INT;
    DECLARE @string AS VARCHAR(MAX) = '';

    WHILE LEN(@input) > 0
    BEGIN           
        SELECT @pos = CHARINDEX(@delimiter,@input);

        IF(@pos<=0)
            select @pos = len(@input)

        IF(@pos <> LEN(@input))
            SELECT @string = SUBSTRING(@input, 1, @pos-1);
        ELSE
            SELECT @string = SUBSTRING(@input, 1, @pos);

        INSERT INTO @result SELECT @string

        SELECT @input = SUBSTRING(@input, @pos+len(@delimiter), LEN(@input)-@pos)       
    END
    RETURN  
END

Instead of recursive CTEs and while loops, has anyone considered a more set-based approach?

CREATE FUNCTION [dbo].[SplitString]
    (
        @List NVARCHAR(MAX),
        @Delim VARCHAR(255)
    )
    RETURNS TABLE
    AS
        RETURN ( SELECT [Value] FROM 
          ( 
            SELECT 
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
              FROM sys.all_objects) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
          ) AS y
        );

More on split functions, why (and proof that) while loops and recursive CTEs don't scale, and better alternatives, if splitting strings coming from the application layer:

http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx

upvote
  flag
There is a small bug in this procedure for the case where there would be a null value at the end of the string - such as in '1,2,,4,' - as the final value is not parsed. To correct this bug, the expression "WHERE Number <= LEN(@List)" should be replaced with "WHERE Number <= LEN(@List) + 1". – SylvainL
upvote
  flag
@SylvainL I guess that depends on what behavior you want. In my experience, most people want to ignore any trailing commas as they don't really represent a real element (how many copies of a blank string do you need)? Anyway, the real way to do this - if you'll follow the second link - is to step messing around with splitting big ugly strings in slow T-SQL anyway. – Aaron Bertrand
upvote
  flag
Like you have said, most people want to ignore any trailing commas but alas, not all. I suppose that a more complete solution would be to add a parameter to specify what to do in this case but my comment is just a little note to make sure that no one forget about this possibility, as it can be quite real in many cases. – SylvainL
upvote
  flag
I have a weird behavior with that function. If I use directly a string as a parameter -- it works. If I have a varchar, it does not. You can reproduce easily: declare invarchar as varchar set invarchar = 'ta;aa;qq' SELECT Value from [dbo].[SplitString](invarchar, ';') SELECT Value from [dbo].[SplitString]('ta;aa;qq', ';') – Patrick Desjardins
upvote
  flag
I like this approach, but if the number of objects returned by sys.all_objects is less than number of the characters in the input string then it will truncate the string and values will go missing. Since sys.all_objects is just being used as a bit of a hack to generate rows, then there are better ways to do this, e.g. this answer. – knuckles
upvote
  flag
The one thing I don't understand is: Why the outermost CTE? It seems like you're doing SELECT [Value] FROM (SELECT [Value] = ... FROM ...), so it seems like the outer SELECT [Value] FROM is redundant. Is there a performance consideration I'm not seeing? – Justin Morgan
upvote
  flag
@Justin so that you can choose to return the second column (e.g. for troubleshooting, sorting, etc). – Aaron Bertrand
upvote
  flag
@knuckels thats why a numbers table is always handy! – BJury

This is more narrowly-tailored. When I do this I usually have a comma-delimited list of unique ids (INT or BIGINT), which I want to cast as a table to use as an inner join to another table that has a primary key of INT or BIGINT. I want an in-line table-valued function returned so that I have the most efficient join possible.

Sample usage would be:

 DECLARE @IDs VARCHAR(1000);
 SET @IDs = ',99,206,124,8967,1,7,3,45234,2,889,987979,';
 SELECT me.Value
 FROM dbo.MyEnum me
 INNER JOIN dbo.GetIntIdsTableFromDelimitedString(@IDs) ids ON me.PrimaryKey = ids.ID

I stole the idea from http://sqlrecords.blogspot.com/2012/11/converting-delimited-list-to-table.html, changing it to be in-line table-valued and cast as INT.

create function dbo.GetIntIDTableFromDelimitedString
    (
    @IDs VARCHAR(1000)  --this parameter must start and end with a comma, eg ',123,456,'
                        --all items in list must be perfectly formatted or function will error
)
RETURNS TABLE AS
 RETURN

SELECT
    CAST(SUBSTRING(@IDs,Nums.number + 1,CHARINDEX(',',@IDs,(Nums.number+2)) - Nums.number - 1) AS INT) AS ID 
FROM   
     [master].[dbo].[spt_values] Nums
WHERE Nums.Type = 'P' 
AND    Nums.number BETWEEN 1 AND DATALENGTH(@IDs)
AND    SUBSTRING(@IDs,Nums.number,1) = ','
AND    CHARINDEX(',',@IDs,(Nums.number+1)) > Nums.number;

GO

here is a version that can split on a pattern using patindex, a simple adaptation of the post above. I had a case where I needed to split a string that contained multiple separator chars.


alter FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(1000), @splitPattern varchar(10) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE PATINDEX(@splitPattern, @stringToSplit) > 0
 BEGIN
  SELECT @pos  = PATINDEX(@splitPattern, @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END
select * from dbo.splitstring('stringa/stringb/x,y,z','%[/,]%');

result looks like this

stringa stringb x y z

I needed a quick way to get rid of the +4 from a zip code.

UPDATE #Emails 
  SET ZIPCode = SUBSTRING(ZIPCode, 1, (CHARINDEX('-', ZIPCODE)-1)) 
  WHERE ZIPCode LIKE '%-%'

No proc... no UDF... just one tight little inline command that does what it must. Not fancy, not elegant.

Change the delimiter as needed, etc, and it will work for anything.

2 upvote
  flag
This isn't what the question is about. The OP has a value like '234,542,23' and they want to split it out into three rows ... 1st row: 234, 2nd row: 542, 3rd row: 23. Its a tricky thing to do in SQL. – codeulike

Personnaly I use this function :

ALTER FUNCTION [dbo].[CUST_SplitString]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1) 
        FROM Split
        WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)

I have developed a double Splitter (Takes two split characters) as requested Here. Could be of some value in this thread seeing its the most referenced for queries relating to string splitting.

CREATE FUNCTION uft_DoubleSplitter 
(   
    -- Add the parameters for the function here
    @String VARCHAR(4000), 
    @Splitter1 CHAR,
    @Splitter2 CHAR
)
RETURNS @Result TABLE (Id INT,MId INT,SValue VARCHAR(4000))
AS
BEGIN
DECLARE @FResult TABLE(Id INT IDENTITY(1, 1),
                   SValue VARCHAR(4000))
DECLARE @SResult TABLE(Id INT IDENTITY(1, 1),
                   MId INT,
                   SValue VARCHAR(4000))
SET @String = @String+@Splitter1

WHILE CHARINDEX(@Splitter1, @String) > 0
    BEGIN
       DECLARE @WorkingString VARCHAR(4000) = NULL

       SET @WorkingString = SUBSTRING(@String, 1, CHARINDEX(@Splitter1, @String) - 1)
       --Print @workingString

       INSERT INTO @FResult
       SELECT CASE
            WHEN @WorkingString = '' THEN NULL
            ELSE @WorkingString
            END

       SET @String = SUBSTRING(@String, LEN(@WorkingString) + 2, LEN(@String))

    END
IF ISNULL(@Splitter2, '') != ''
    BEGIN
       DECLARE @OStartLoop INT
       DECLARE @OEndLoop INT

       SELECT @OStartLoop = MIN(Id),
            @OEndLoop = MAX(Id)
       FROM @FResult

       WHILE @OStartLoop <= @OEndLoop
          BEGIN
             DECLARE @iString VARCHAR(4000)
             DECLARE @iMId INT

             SELECT @iString = SValue+@Splitter2,
                   @iMId = Id
             FROM @FResult
             WHERE Id = @OStartLoop

             WHILE CHARINDEX(@Splitter2, @iString) > 0
                BEGIN
                    DECLARE @iWorkingString VARCHAR(4000) = NULL

                    SET @IWorkingString = SUBSTRING(@iString, 1, CHARINDEX(@Splitter2, @iString) - 1)

                    INSERT INTO @SResult
                    SELECT @iMId,
                         CASE
                         WHEN @iWorkingString = '' THEN NULL
                         ELSE @iWorkingString
                         END

                    SET @iString = SUBSTRING(@iString, LEN(@iWorkingString) + 2, LEN(@iString))

                END

             SET @OStartLoop = @OStartLoop + 1
          END
       INSERT INTO @Result
       SELECT MId AS PrimarySplitID,
            ROW_NUMBER() OVER (PARTITION BY MId ORDER BY Mid, Id) AS SecondarySplitID ,
            SValue
       FROM @SResult
    END
ELSE
    BEGIN
       INSERT INTO @Result
       SELECT Id AS PrimarySplitID,
            NULL AS SecondarySplitID,
            SValue
       FROM @FResult
    END
RETURN

Usage:

--FirstSplit
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&',NULL)

--Second Split
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&','=')

Possible Usage (Get second value of each split):

SELECT fn.SValue
FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===', '&', '=')AS fn
WHERE fn.mid = 2

The easiest way to do this is by using XML format.

1. Converting string to rows without table

QUERY

DECLARE @String varchar(100) = 'String1,String2,String3'
-- To change ',' to any other delimeter, just change ',' to your desired one
DECLARE @Delimiter CHAR = ','    

SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' 
FROM  
(     
     SELECT CAST ('<M>' + REPLACE(@String, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data            
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)

RESULT

 x---------x
 | Value   |
 x---------x
 | String1 |
 | String2 |
 | String3 |
 x---------x

2. Converting to rows from a table which have an ID for each CSV row

SOURCE TABLE

 x-----x--------------------------x
 | Id  |           Value          |
 x-----x--------------------------x
 |  1  |  String1,String2,String3 |
 |  2  |  String4,String5,String6 |     
 x-----x--------------------------x

QUERY

-- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
DECLARE @Delimiter CHAR = ','

SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' 
FROM  
(     
     SELECT ID,CAST ('<M>' + REPLACE(VALUE, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data            
     FROM TABLENAME
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)

RESULT

 x-----x----------x
 | Id  |  Value   |
 x-----x----------x
 |  1  |  String1 |
 |  1  |  String2 |  
 |  1  |  String3 |
 |  2  |  String4 |  
 |  2  |  String5 |
 |  2  |  String6 |     
 x-----x----------x
upvote
  flag
This approach will break if @String contains forbidden characters... I just posted an answer to overcome this issue. – Shnugo

There is a correct version on here but I thought it would be nice to add a little fault tolerance in case they have a trailing comma as well as make it so you could use it not as a function but as part of a larger piece of code. Just in case you're only using it once time and don't need a function. This is also for integers (which is what I needed it for) so you might have to change your data types.

DECLARE @StringToSeperate VARCHAR(10)
SET @StringToSeperate = '1,2,5'

--SELECT @StringToSeperate IDs INTO #Test

DROP TABLE #IDs
CREATE TABLE #IDs (ID int) 

DECLARE @CommaSeperatedValue NVARCHAR(255) = ''
DECLARE @Position INT = LEN(@StringToSeperate)

--Add Each Value
WHILE CHARINDEX(',', @StringToSeperate) > 0
BEGIN
    SELECT @Position  = CHARINDEX(',', @StringToSeperate)  
    SELECT @CommaSeperatedValue = SUBSTRING(@StringToSeperate, 1, @Position-1)

    INSERT INTO #IDs 
    SELECT @CommaSeperatedValue

    SELECT @StringToSeperate = SUBSTRING(@StringToSeperate, @Position+1, LEN(@StringToSeperate)-@Position)

END

--Add Last Value
IF (LEN(LTRIM(RTRIM(@StringToSeperate)))>0)
BEGIN
    INSERT INTO #IDs
    SELECT SUBSTRING(@StringToSeperate, 1, @Position)
END

SELECT * FROM #IDs
upvote
  flag
if you were to SET @StringToSeperate = @StringToSeperate+',' immediately before the WHILE loop I think you might be able to eliminate the "add last value" block. See also my sol'n on github – mpag
upvote
  flag
Which answer is this based on? There are a lot of answers here, and it's a bit confusing. Thanks. – jpaugh

I modified +Andy Robinson's function a little bit. Now you can select only required part from returning table:

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )

RETURNS

 @returnList TABLE ([numOrder] [tinyint] , [Name] [nvarchar] (500)) AS
BEGIN

 DECLARE @name NVARCHAR(255)

 DECLARE @pos INT

 DECLARE @orderNum INT

 SET @orderNum=0

 WHILE CHARINDEX('.', @stringToSplit) > 0

 BEGIN
    SELECT @orderNum=@orderNum+1;
  SELECT @pos  = CHARINDEX('.', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @orderNum,@name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END
    SELECT @orderNum=@orderNum+1;
 INSERT INTO @returnList
 SELECT @orderNum, @stringToSplit

 RETURN
END


Usage:

SELECT Name FROM dbo.splitstring('ELIS.YD.CRP1.1.CBA.MDSP.T389.BT') WHERE numOrder=5

You can Use this function:

        CREATE FUNCTION SplitString
        (    
           @Input NVARCHAR(MAX),
           @Character CHAR(1)
          )
            RETURNS @Output TABLE (
            Item NVARCHAR(1000)
          )
        AS
        BEGIN

      DECLARE @StartIndex INT, @EndIndex INT
      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

The easiest way:

  1. Install SQL Server 2016
  2. Use STRING_SPLIT https://msdn.microsoft.com/en-us/library/mt684588.aspx

It works even in express edition :).

upvote
  flag
Don't forget to set "Compatibility level" to SQL Server 2016 (130) - in management studio, right click on database, properties / options / compatibility level. – Tomino
upvote
  flag
The original post said for SQL 2008 R2. Installing SQL 2016 may not be an option – Shawn Gavett

Finally the wait is over in SQL Server 2016 they have introduced Split string function : STRING_SPLIT

select * From STRING_SPLIT ('a,b', ',') cs 

All the other methods to split string like XML, Tally table, while loop, etc.. has been blown away by this STRING_SPLIT function.

Here is an excellent article with performance comparison : Performance Surprises and Assumptions : STRING_SPLIT

3 upvote
  flag
obviously answers question of how to split string for those with updated servers, but those of us still stuck on 2008/2008R2, will have to go with one of the other answers here. – mpag
upvote
  flag
You need to take a look at the compatibility level in your database. If it is lower than 130 you won't be able to use the STRING_SPLIT function. – Luis Teijon
upvote
  flag
Actually, if the compatibility isn't 130 and you're running 2016 (or Azure SQL) you can set the compatibility up to 130 using: ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130 – Michieal

Here is an example that you can use as function or also you can put the same logic in procedure. --SELECT * from [dbo].fn_SplitString ;

CREATE FUNCTION [dbo].[fn_SplitString]
(@CSV VARCHAR(MAX), @Delimeter VARCHAR(100) = ',')
       RETURNS @retTable TABLE 
(

    [value] VARCHAR(MAX) NULL
)AS

BEGIN

DECLARE
       @vCSV VARCHAR (MAX) = @CSV,
       @vDelimeter VARCHAR (100) = @Delimeter;

IF @vDelimeter = ';'
BEGIN
    SET @vCSV = REPLACE(@vCSV, ';', '~!~#~');
    SET @vDelimeter = REPLACE(@vDelimeter, ';', '~!~#~');
END;

SET @vCSV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vCSV, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'), '''', '&apos;'), '"', '&quot;');

DECLARE @xml XML;

SET @xml = '<i>' + REPLACE(@vCSV, @vDelimeter, '</i><i>') + '</i>';

INSERT INTO @retTable
SELECT
       x.i.value('.', 'varchar(max)') AS COLUMNNAME
  FROM @xml.nodes('//i')AS x(i);

 RETURN;
END;
upvote
  flag
This approach will break if @vCSV contains forbidden characters... I just posted an answer to overcome this issue. – Shnugo

/*

Answer to T-SQL split string
Based on answers from Andy Robinson and AviG
Enhanced functionality ref: LEN function not including trailing spaces in SQL Server
This 'file' should be valid as both a markdown file and an SQL file

```

*/

CREATE FUNCTION dbo.splitstring ( --CREATE OR ALTER
    @stringToSplit NVARCHAR(MAX)
) RETURNS @returnList TABLE ([Item] NVARCHAR (MAX))
AS BEGIN
    DECLARE @name NVARCHAR(MAX)
    DECLARE @pos BIGINT
    SET @stringToSplit = @stringToSplit + ','             -- this should allow entries that end with a `,` to have a blank value in that "column"
    WHILE ((LEN(@stringToSplit+'_') > 1)) BEGIN           -- `+'_'` gets around LEN trimming terminal spaces. See URL referenced above
        SET @pos = COALESCE(NULLIF(CHARINDEX(',', @stringToSplit),0),LEN(@stringToSplit+'_')) -- COALESCE grabs first non-null value
        SET @name = SUBSTRING(@stringToSplit, 1, @pos-1)  --MAX size of string of type nvarchar is 4000 
        SET @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, 4000) -- With SUBSTRING fn (MS web): "If start is greater than the number of characters in the value expression, a zero-length expression is returned."
        INSERT INTO @returnList SELECT @name --additional debugging parameters below can be added
        -- + ' pos:' + CAST(@pos as nvarchar) + ' remain:''' + @stringToSplit + '''(' + CAST(LEN(@stringToSplit+'_')-1 as nvarchar) + ')'
    END
    RETURN
END
GO

/*

```

Test cases: see URL referenced as "enhanced functionality" above

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,,b')

Item | L
---  | ---
a    | 1
     | 0
b    | 1

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,,')

Item | L   
---  | ---
a    | 1
     | 0
     | 0

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,, ')

Item | L   
---  | ---
a    | 1
     | 0
     | 1

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,, c ')

Item | L   
---  | ---
a    | 1
     | 0
 c   | 3

*/

All the functions for string splitting that use some kind of Loop-ing (iterations) have bad performance. They should be replaced with set-based solution.

This code executes excellent.

CREATE FUNCTION dbo.SplitStrings
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO
upvote
  flag
This approach will break if @List contains forbidden characters... I just posted an answer to overcome this issue. – Shnugo

The often used approach with XML elements breaks in case of forbidden characters. This is an approach to use this method with any kind of character, even with the semicolon as delimiter.

The trick is, first to use SELECT SomeString AS [*] FOR XML PATH('') to get all forbidden characters properly escaped. That's the reason, why I replace the delimiter to a magic value to avoid troubles with ; as delimiter.

DECLARE @Dummy TABLE (ID INT, SomeTextToSplit NVARCHAR(MAX))
INSERT INTO @Dummy VALUES
 (1,N'A&B;C;D;E, F')
,(2,N'"C" & ''D'';<C>;D;E, F');

DECLARE @Delimiter NVARCHAR(10)=';'; --special effort needed (due to entities coding with "&code;")!

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

The result

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.