How To Split Delimited String In SQL Server Using STRING_SPLIT

STRING_SPLIT, a table-valued function (TVF), splits delimited list into a single column table rows. SQL Server 2016 added STRING_SPLIT function, simplifying the problem of splitting rows, delimited by a separator into multiple rows of single values. Furthermore, Normalization Forms never recommends storing a delimited row in a table. In this case, a denormalized table can be converted to normalized one using STRING_SPLIT function.

While, former methods to split delimited list using cursors, XML methods, CTE s involved writing longer code and performed poorly on large data set. In short, STRING_SPLIT function is a go-to way to work with the delimited list going ahead with SQL Server 2016.

Syntax

STRING_SPLIT (String, Delimiter)

Arguments

String - Delimited list of string type.

Delimiter - Single character value act as a separator between delimited string.

STRING_SPLIT Function – Points To Ponder

1.STRING_SPLIT accepts only Single character delimiter. Parsing a delimited list involving multi-character delimiters is not permitted by design. Therefore, we can split a list separated by ‘,’ (Comma) but not like ‘,|’ (Comma-Pipe).

2.Value is default column name for result set generated by STRING_SPLIT function.

3.STRING_SPLIT function works with SQL Server 2016 and later editions. Make sure you are on SQL Server database with compatibility 130 or above using below query. Otherwise, you will find an error Invalid object name ‘STRING_SPLIT’.

Check Database Compatibility Level

USE tempdb;
GO
--Gives 130 or above thus STRING_SPLIT Function Works
SELECT compatibility_level  
FROM sys.databases WHERE name = 'tempdb';  
GO  

--If you're on SQL Server 2016 and want to change compability
--level to 130 to make STRING_SPLIT work.
Use [DatabaseName];
GO

ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 130;
GO

STRING_SPLIT – Examples

Demonstrating STRING_SPLIT function with a few helpful examples here.

1.

STRING_SPLIT – Split Delimited List In a Variable

@PlayerNames variable stores the list of player names separated by a comma delimiter. Furthermore, using the following query, we are splitting comma delimited list into a single column table with multiple rows. Most importantly, notice the returned table has a default column name value return by STRING_SPLIT table-valued function.

--Split Delimited List Stored In a Variable
USE tempdb;
GO

DECLARE @PlayerNames AS VARCHAR(100) = 'Federer,Nadal,Djokovic,Murry';

SELECT *
FROM   STRING_SPLIT(@PlayerNames,',');

Result Set

2.

STRING_SPLIT – Split Delimited List In a Single Column

@Players, table variable store the list of player names separated by a pipe ‘|’ delimiter. In the following query, using CROSS APPLY operator to work with STRING_SPLIT table-valued function. APPLY operators are specially designed to work with TVFs as simple JOINs don’t work. Passing the Name column name and pipe delimiter as arguments to STRING_SPLIT function.

--STRING_SPLIT - Split Delimited List In a Single Column Table
USE tempdb;
GO

DECLARE @Players TABLE
(
 Name     VARCHAR(50)
);

INSERT INTO @Players VALUES
 ('Federer|Murry')
,('Nadal|Djokovic')

SELECT * FROM @Players;

--Using STRING_SPLIT with CROSS APPLY 
SELECT  value 
FROM   @Players CROSS APPLY STRING_SPLIT(Name,'|');

Result Set

3.

STRING_SPLIT – Split Delimited List In a Multiple Columns

In the following query, the @Records table has got two columns. Player names and their list of won trophies stored as comma separated values. Using STRING_SPLIT function we convert trophy names into a single column and associating it with player name.

--Split Delimited List In a Multiple Columns Table
USE tempdb;
GO

DECLARE @Records TABLE
(
 Name     VARCHAR(50)
,Trophies VARCHAR(255)
);

INSERT INTO @Records VALUES
 ('Federer' ,'Wells,Miami,Halle')
,('Nadal'   ,'Madrid,Italian')
,('Djokovic','Paris');

SELECT * FROM @Records;

--Using STRING_SPLIT with CROSS APPLY
SELECT Name, value as TrophyName
FROM   @Records CROSS APPLY STRING_SPLIT(Trophies,',');

Result Set

4.

STRING_SPLIT – Split Delimited List and WHERE Clause

Reusing the previous query, and adding a WHERE clause to filter out the records to fetch rows only for player name Federer.

-- STRING_SPLIT - Split Delimited List and WHERE Clause
--Split Delimited List and WHERE Clause
USE tempdb;
GO

DECLARE @Records TABLE
(
 Name     VARCHAR(50)
,Trophies VARCHAR(255)
);

INSERT INTO @Records VALUES
 ('Federer' ,'Wells,Miami,Halle')
,('Nadal'   ,'Madrid,Italian')
,('Djokovic','Paris');

SELECT * FROM @Records;

--Using STRING_SPLIT with CROSS APPLY
SELECT Name, value as TrophyName
FROM   @Records CROSS APPLY STRING_SPLIT(Trophies,',')
WHERE  Name = 'Federer';

Result Set

5.

STRING_SPLIT – Split Delimited List and IN Clause

In the following query, here STRING_SPLIT splits comma-delimited list and adding IN clause to filter out the rows based on value column returned by STRING_SPLIT function.

-- STRING_SPLIT - Split Delimited List and IN Clause
--Split Delimited List and IN Clause
USE tempdb;
GO

DECLARE @Records TABLE
(
 Name     VARCHAR(50)
,Trophies VARCHAR(255)
);

INSERT INTO @Records VALUES
 ('Federer' ,'Wells,Miami,Halle')
,('Nadal'   ,'Madrid,Italian')
,('Djokovic','Paris');

SELECT * FROM @Records;

--Using STRING_SPLIT with CROSS APPLY
SELECT Name, value as TrophyName
FROM   @Records CROSS APPLY STRING_SPLIT(Trophies,',')
WHERE  value IN ('Miami','Halle','Madrid','Paris');

Result Set

6.

6. STRING_SPLIT – Split Delimited List and ORDER BY Clause

In particular, STRING_SPLIT doesn’t sort the return values from a delimited list. If you wish to sort the delimited rows then specify ORDER BY clause explicitly instead of relying on the default order returned by the query.

In the following query, we have added ORDER BY clause on TrophyName column to sort. Most importantly notice that the use of TrophyName column in ORDER BY instead of value column. In this case, Logical Processing Order in SQL Server allows us to refer the alias column name in ORDER BY clause. ORDER BY clause is evaluated after SELECT, therefore allowing us to use TrophyName in ORDER BY clause.

-- STRING_SPLIT - Split Delimited List and ORDER BY Clause
--Split Delimited List and ORDER BY Clause
USE tempdb;
GO

DECLARE @Records TABLE
(
 Name     VARCHAR(50)
,Trophies VARCHAR(255)
);

INSERT INTO @Records VALUES
 ('Federer' ,'Wells,Miami,Halle')
,('Nadal'   ,'Madrid,Italian')
,('Djokovic','Paris');

SELECT * FROM @Records;

--Using STRING_SPLIT with CROSS APPLY
SELECT Name, value as TrophyName
FROM   @Records CROSS APPLY STRING_SPLIT(Trophies,',')
ORDER BY TrophyName;

Result Set

7.

STRING_SPLIT – Split Delimited List and GROUP BY Clause

Following query demonstrates the use of GROUP BY clause with STRING_SPLIT function. First, separating the delimited list into multiple rows and counting the trophy names grouped by Name column.

--STRING_SPLIT - Split Delimited List and GROUP BY Clause
--Split Delimited List and GROUP BY
USE tempdb;
GO

DECLARE @Records TABLE
(
 Name     VARCHAR(50)
,Trophies VARCHAR(255)
);

INSERT INTO @Records VALUES
 ('Federer' ,'Wells,Miami,Halle')
,('Nadal'   ,'Madrid,Italian')
,('Djokovic','Paris');

SELECT * FROM @Records;

--Using STRING_SPLIT with CROSS APPLY
SELECT Name, COUNT(value) as NoOfTrophiesWon
FROM   @Records CROSS APPLY STRING_SPLIT(Trophies,',')
GROUP BY Name
ORDER BY NoOfTrophiesWon DESC;

Result Set

8.

STRING_SPLIT – Split Delimited List and INNER JOIN

Below query demonstrates the use of INNER JOIN with STRING_SPLIT function. Joining the Country column from @Venue table with value column returned by STRING_SPLIT function based on trophy name.

--Split Delimited List and INNER JOIN 
USE tempdb;
GO

DECLARE @Venue TABLE
(
 Trophy  VARCHAR(50)
,Country VARCHAR(50)
);

INSERT INTO @Venue VALUES
 ('Wells','California')
,('Miami','Florida')
,('Halle','Germeny')
,('Madrid','Madrid')
,('Italian','Rome')
,('Paris','Paris');

DECLARE @Records TABLE
(
 Name     VARCHAR(50)
,Trophies VARCHAR(255)
);

INSERT INTO @Records VALUES
 ('Federer' ,'Wells,Miami,Halle')
,('Nadal'   ,'Madrid,Italian')
,('Djokovic','Paris');

SELECT * FROM @Records;

--Using STRING_SPLIT with CROSS APPLY and INNER JOIN
SELECT Name, Tr.value as TrophyName, V.Country 
FROM   @Records CROSS APPLY STRING_SPLIT(Trophies,',') as Tr
                INNER JOIN @Venue as V ON V.Trophy = Tr.value;

Result Set

Summary

To summarize, we have learned to use STRING_SPLIT function with various clauses and filter conditions to split delimited list with single character seperator. If you like this post you may read through Tech-Recipes Database archive posts to learn some more useful stuff.