String 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,
  1. SELECT value AS 'Flavor' FROM STRING_SPLIT('Chocolate,Vanilla,Strawberry'',');  
Results
 
String Function In SQL Server 
 
The Plan
 
Here is what the plan looks like. It's very straight forward and simple.
 
String Function In SQL Server 
 

Custom Function

 
Now here is a home-grown version you may find in some environments. You can see its much less efficient.
  1. /****** Object:  UserDefinedFunction [dbo].[fnSplit]    Script Date: 2/11/2020 6:26:45 PM ******/  
  2. SET ANSI_NULLS ON  
  3. GO  
  4. SET QUOTED_IDENTIFIER ON  
  5. GO  
  6. ALTER FUNCTION [dbo].[fnSplit](  
  7.     @sInputList VARCHAR(8000) -- List of delimited items  
  8.   , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items  
  9. RETURNS @List TABLE (item VARCHAR(8000))  
  10. BEGIN  
  11. DECLARE @sItem VARCHAR(8000)  
  12. WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0  
  13.  BEGIN  
  14.  SELECT  
  15. @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))  
  16.  IF LEN(@sItem) > 0  
  17.   INSERT INTO @List SELECT @sItem  
  18.  END  
  19. IF LEN(@sInputList) > 0  
  20.  INSERT INTO @List SELECT @sInputList -- Put the last item in  
  21. RETURN  
  22. 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.
 
String Function In SQL Server 
 

The Plan

 
It's a little more complicated then the straight forward one we saw above including a sequence.
 
String Function In SQL Server 
 
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.


Similar Articles
Denny Cherry and Associates
Expert Consultants From HA to DR to up-time to SQL virtualization to scalability.