String Split Function in SQL Server

Published On: 2020-02-12By:

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', ',');

Results

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

The Results

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.

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link