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

SQL Server: Split Delimited Strings SP

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
SQL Server: Split Delimited Strings Example

Related Articles


6 thoughts on “How To Split Delimited Strings in SQL Server?”

  1. Thank you very much!

    We were using a similar function, but it was very slow when the number of elements in the string was 30 000 or so.

    Your function accomplishes the same goal much-much faster.

    Reply
  2. Your function fails if a ‘<' or other xml-defined char is included, ie,

    select * from dbo.fn_splitString(' quick brown fox’,’ ‘)

    Reply
  3. Throwing Error: Msg 4121, Level 16, State 1, Line 4
    Cannot find either column “i” or the user-defined function or aggregate “i.VALUE”, or the name is ambiguous.

    Reply

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.