Blog Post

SQL Split String Function (STRING_SPLIT in 2016)

,

* 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.

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...

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating