Split string with delimiter into one column with SQL Server

How to split a string with delimiter with XML or STRING_SPLIT functions with SQL Server ?

Split a string with delimiter, like comma or semicolon, into one column using SQL Server. With a T-SQL query, it is possible to cut a string compounded of text, delimited by commas, semicolons, tabulations, dash, underscore, or even dots. It can basically be any special character that defines the delimited text.

1. How to split a string with delimiter into one SQL Server column ?

Indeed, for example, the easy and efficient way is to use SQL Server XML built-in functions like CONVERT() and NODES(). Then use a Transact-SQL query to split the text into multiple lines. Indeed, the power of XML functions allows the SQL developer to parse and structure text more easily. For example, let’s cut easily this string containing the ten biggest US cities delimited with semicolons:

“New York; Los Angeles; Chicago; Houston; Phoenix; Philadelphia; San Antonio; San Diego; Dallas; San Jose”

This example works with any type of separators also called delimiters. The most used ones are:

• Comma-delimited file
• Semi-column delimited file
• Tab-delimited file
• Vertical bar delimited file
• Dash delimited file

For instance, this Transact-SQL query splits the string containing the list of cities delimited by semicolons.

-- Declare the variables
DECLARE @String nvarchar(max),
@Delimiter char(1),
@XMLString xml;

-- Initialize the string and the delimiter, here it's the semicolon character 
SELECT @String = 'New York;Los Angeles;Chicago;Houston;Phoenix;Philadelphia;San Antonio;San Diego;Dallas;San Jose',
@Delimiter = ';'

-- XML String construction using T-SQL CONVERT() function
SET @XMLString = 
CONVERT(xml,'<root><city>' +
REPLACE(@String,@Delimiter,'</city><city>') +
'</city></root>');

SELECT @XMLString;

-- Select of the result by parsing the @XMLString variable with the .value() .nodes() XML functions
SELECT Result.value('.','varchar(20)') AS CITY
FROM @XMLString.nodes('/root/city') AS T(Result);

Indeed, to understand the logic of the query, the @XMLString variable only contains the initial list of cities. However, this time the list is surrounded by XML tags <root></root> and <city></city>.

<root>
	<city>New York</city>
	<city>Los Angeles</city>
	<city>Chicago</city>
	<city>Houston</city>
	<city>Phoenix</city>
	<city>Philadelphia</city>
	<city>San Antonio</city>
	<city>San Diego</city>
	<city>Dallas</city>
	<city>San Jose</city>
</root>

Similarly, you might be interested on how to remove line break from a SQL Server string in order to store it in a single line of a table.

How to insert or remove SQL Server line breaks from text ?

2. Use the STRING_SPLIT function in SQL Server 2016 and newer

However, It also depends of the SQL Server version you use to manage your database. For example, I have been using T-SQL user-defined functions for years before the 2016 version came out.

Indeed, starting in SQL Server 2016 a new built-in function allows this feature. It’s the String Split function. So, this is an example to achieve the same result in one unique line of code:

-- Variables declaration
DECLARE 
@String nvarchar(max), 
@Separator char(1);

-- Initialisation of the string and the Separator
SET @String = 'New York;Los Angeles;Chicago;Houston;Phoenix;Philadelphia;San Antonio;San Diego;Dallas;San Jose';
SET @Separator = ';';

-- Use the Split String here instead of the XML solution
SELECT @String = STRING_SPLIT ( @String , @Separator );

3. Split the text variable into multiples columns

What if you need to store each value in a separate column, for example to prepare the import of csv files with a header ? Here is an example of how to select each value in the @String variable into a separate column.

-- Declare the variables
DECLARE @String nvarchar(max),
@Delimiter char(1);

-- Initialize the string and the delimiter, here it's the semicolon character
SELECT @String = 'New York;Los Angeles;Chicago;Houston;Phoenix;Philadelphia;San Antonio;San Diego;Dallas;San Jose',
@Delimiter = ';'

-- Split the string into multiple columns
WITH cte AS (
SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as id
FROM STRING_SPLIT(@String, @Delimiter)
)
SELECT
MAX(CASE WHEN id = 1 THEN value END) AS column_1,
MAX(CASE WHEN id = 2 THEN value END) AS column_2,
MAX(CASE WHEN id = 3 THEN value END) AS column_3,
MAX(CASE WHEN id = 4 THEN value END) AS column_4,
MAX(CASE WHEN id = 5 THEN value END) AS column_5,
MAX(CASE WHEN id = 6 THEN value END) AS column_6,
MAX(CASE WHEN id = 7 THEN value END) AS column_7,
MAX(CASE WHEN id = 8 THEN value END) AS column_8,
MAX(CASE WHEN id = 9 THEN value END) AS column_9,
MAX(CASE WHEN id = 10 THEN value END) AS column_10
FROM cte

The following example will only work if you know the exact number of columns you will need ahead of time and the maximum number of columns is 10.

If you have more values in your string, you will need to add more columns. Also, you can use dynamic SQL to generate the query if you don’t know the number of columns in advance.

Conclusion on how to split string with delimiter in T-SQL

To summarize, this article shows how to split a long delimited text with a SQL Server query. The first example uses the XML functions and it’s easy to copy and paste. Hence reuse it for a real project scenario or proof of concept. Similarly, the second example simply uses the Split_String SQL Server native function.

How to calculate the difference between two dates with T-SQL?