How to Split Delimited String into One Column in SQL Server ?

How to split data from a unique string in line with a delimiter into one column using SQL Server ?

Tutorial on how to split a delimited string into one unique column in SQL Server, the delimiter used is usually a semicolon or a comma. With a T-SQL query in SQL Server 2012, it is possible to cut a string compounded of text based on a special character with XML. It can be delimited by commas, semicolons, tabulations, dash, underscore, or even dots. It can basically be any special character that defines the delimited text. This tutorial is for SQL Server versions prior to 2016, i.e. SQL Server 2008 and SQL Server 2012 for example.

1. About delimited strings in SQL Server 2008 and 2012

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

And this XML solution allows you to split texts on versions prior to SQL Server 2016, such as : SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014.

2. T-SQL query to split string by delimiter into a column

For instance, this Transact-SQL query splits the string containing the list of cities delimited by semicolons using the XML built-in functions.

-- 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);

3. Intermediate result of the split string in XML format

Indeed, to understand the logic of the query, this delimited string was initially contained in the @String variable, as simple text. And the @XMLString variable only contains the initial list of cities in XML format. However, this time the list is surrounded by XML tags <root></root> and <city></city>. XML is a famous complex data format commonly used in data exchange files.

<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>

Different ways to split a delimited text

To summarize, this article shows how to split a delimited string into one unique column in SQL Server. 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 for SQL Server 2016 and higher versions, you can simply use the Split_String native function.

Similarly, you might be interested to learn how to add or remove line break from SQL Server strings in order to store it in a single line of a table. And at the same time avoid line offsets when importing files.

How To Manage Line Breaks in SQL Server ?