String Split Function in SQL Server

Did you know that a native STRING_SPLIT function built into SQL Server was added into SQL Server 2016? As a consultant I see so much code that call out to a scalar function that are used to split out string delimited variables into a usable list. For those that use this method I suggest you look at this function. STRING_SPLIT is a table valued function that returns a single column of your string values split out by the delimiter. This is an unusual bit of T-SQL, in that compatibility level 130 or higher is required for its use (Microsoft didn’t want to induce breaking changes into existing user code). Using this method is far more efficient and can be executed without calling  a scalar function.

The Syntax

STRING_SPLIT ( string , separator)

How to use it

SELECT value AS 'Flavor' FROM STRING_SPLIT('Chocolate,Vanilla,Strawberry', ',');

The Plan

Here is what the plan looks like. It’s very straight forward and simple.

Custom Function

Now here is a home-grown version you may find in some environments. You can see its much less efficient.

/****** Object:  UserDefinedFunction [dbo].[fnSplit]    Script Date: 2/11/2020 6:26:45 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[fnSplit](

    @sInputList VARCHAR(8000) -- List of delimited items

  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items

) RETURNS @List TABLE (item VARCHAR(8000))



BEGIN

DECLARE @sItem VARCHAR(8000)

WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0

 BEGIN

 SELECT

  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),

  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0

  INSERT INTO @List SELECT @sItem

 END

IF LEN(@sInputList) > 0

 INSERT INTO @List SELECT @sInputList -- Put the last item in

RETURN

END

Results are the same. But note your field name will be the same every time, with the new function you get a little more flexibility in this.

The Plan

It’s a little more complicated then the straight forward one we saw above including a sequence.

There are many different ways to write a split function to make things work, however, now that SQL Server has given us one, I highly encourage you to take a look at it. When performance tuning be sure to take a look at what you’ve always done in your code and look for ways to improve it such as this. You can learn more and see more examples here on docs.microsoft.com.