SQL Split String Function (STRING_SPLIT in 2016)

---

Download xSQL Compare Bundle, starting from $0!

---

* originally published in our old blog in August 2011 - updated with SQL Server 2016 reference

As a SQL Server developer you are often faced with the need to split a string of values that may be separated by comma, space, tab, or any other separator. In 2011 we published a simple table-valued function (SplitString see below) that takes a string and a divider as parameters and returns a table containing the values into a list form (one value for each row). The parameters are defined as a varchar(1024) for the string of values and char(1) for the divider but you can change those based on your needs.

As of SQL Server 2016 a new function STRING_SPLIT ( string , separator )  was introduced that does the same thing. So if you are using SQL Server 2016 use the available STRING_SPLIT function (you can read more about it here: https://msdn.microsoft.com/en-us/library/mt684588.aspx).
However, if you are using an older version of SQL Server you can take advantage of our SplitString function:

CREATE FUNCTION SplitString 
(
    @SeparatedValues VARCHAR(1024),
    @Divider CHAR(1)
)
RETURNS    @ListOfValues TABLE ([value] VARCHAR(50))
AS  BEGIN 
      DECLARE @DividerPos1 int, @DividerPos2 int
      SET @DividerPos1 = 1
      SET @DividerPos2 = CHARINDEX(@Divider, @SeparatedValues, 0)

     WHILE @DividerPos2 > 0
           BEGIN 
                  INSERT INTO @ListOfValues VALUES (SUBSTRING(@SeparatedValues, @DividerPos1, @DividerPos2 - @DividerPos1))
                  SET @DividerPos1 = @DividerPos2 + 1
                  SET @DividerPos2 = CHARINDEX(@Divider, @SeparatedValues, @DividerPos1)
            END
           -- Now get the last value if there is onw
                  IF @DividerPos1 <= LEN(@SeparatedValues) 
                       INSERT INTO @ListOfValues VALUES (SUBSTRING(@SeparatedValues, @DividerPos1, LEN(@SeparatedValues) - @DividerPos1 + 1))

        RETURN
  END
GO

Once you create the function you can call it like this:

SELECT * FROM [SplitString] (@mystring, @myseparator)

or with hardcoded values (in this example the separator is a vertical line):

SELECT * FROM [SplitString] ('value1|value2|value3', '|')

This will return:
   value1
   value2
   value3
 
Note that if the string starts with a divider like '|value1|value2|value3' then the first value returned will be a blank value.

CodeProject You can of course insert those values directly into a table either via INSERT INTO <mytable> ... SELECT * FROM [SplitString]... or into a temp table via SELECT * INTO #mytemptable...FROM...