How To Split Delimited Strings in SQL Server?

As SQL Server programmers, we often need to split the string or text, based on delimiters. There are so many ways to split delimited strings. Here are a couple of methods to split the delimited string in newer and older versions of SQL Server.

On SQL Server 2016 And Higher (2106, 2017,…)

In SQL Server 2016, Microsoft introduced the STRING_SPLIT() inbuilt function to split a string using a specific delimiter. This is the easiest method to split delimited string in SQL Server. To use STRING_SPLIT, the database should be at least in compatibility level of 130. Check the compatibility of your database before using this function. Here is an example of using STRING_SPLIT.

DECLARE @strInput VARCHAR(100);
SET @strInput = 'Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday'
 
Select VALUE FROM STRING_SPLIT(@strInput, ',')
GO
 
/* Result */
VALUE
--------------
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
 
(7 rows affected)

On SQL Server 2014 And Older

Recently I’ve gone through a simple way to split delimited string in SQL Server 2014 and lower versions written by Clayton in Code Project. That method uses XML and parsed the data for integer, numeric and date-time. My requirement does not need to parse the values. So, I’ve simplified the method, just to split the delimited string and return the result as strings. Here is the simple function which splits the delimited text and returns the result in table format.

Function To Split Delimited Strings

CREATE FUNCTION [dbo].[fn_SplitString]
(@SourceString VARCHAR(MAX)
,@Seperator VARCHAR(25)=’,’
)
RETURNS @ResultTable
TABLE(
[Position] INT IDENTITY(1,1),
[Value] VARCHAR(MAX)
)
AS
/**************************************************************
* Author: Beaulin @ www.MyTecBits.com
* Description: Function to split the delimited text and
* returns teh result in table format
**************************************************************/
BEGIN
DECLARE @w_xml xml;

SET @w_xml = N'<root><i>’ + replace(@SourceString, @Seperator,'</i><i>’) + ‘</i></root>’;

INSERT INTO @ResultTable
([Value])
SELECT
[i].value(‘.’, ‘VARCHAR(MAX)’) AS Value
FROM
@w_xml.nodes(‘//root/i’) AS [Items]([i]);
RETURN;
END;
GO

Examples

Example With Single Character Delimiter

This example splits the text delimited by a single character delimiter like “,” or “|” or “;” or any other character.

Select * from fn_SplitString('Cat|Dog|Tiger|Lion|Leopard', '|'); 

Position    Value 
--------    ------------ 
1      Cat 
2      Dog 
3      Tiger 
4      Lion 
5      Leopard

Example With Multi Character Delimiter

This example splits the text delimited by a single character delimiter like comma with space “, ” or HTML tags like <p>, <li> or any other word or characters.

Select * from fn_SplitString('Cat; Dog; Tiger; Lion; Leapord', '; '); 

Position    Value 
--------    ------------ 
1      Cat 
2      Dog 
3      Tiger 
4      Lion 
5      Leopard

Related Articles

• What is SP_WHO & SP_WHO2?
• SQL Server case-sensitive string comparison.