Split a delimited text with SQL Server

How to split a delimited text in SQL Server? The string to split can have a comma or a semicolon delimiter. Using an SQL Server query, you can cut a string compounded of text, delimited by commas, semicolons, tabulations, dash, underscore, or even dots. It can basically be any character that defines a delimited text.

 

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

How to cut a long text with separators into lines with an XML query?

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

-- Variables declaration
DECLARE @String nvarchar(max),
@Delimiter char(1),
@XMLString xml;

-- Initialisation of 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 it’s 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.

 

Use the String Split function introduced in SQL Server 2016 to split a long delimited text into lines

 

However, please note that 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 );

 

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.

What is a delimited text?

A delimited text is one unique line that has multiple parts. Each part is delimited with a specific symbol, for example, a semi-column, a tabulation, a vertical bar.

What is a delimited text file?

A delimited text file is a file that has multiple columns and every data contained for each line is delimited by a separator. For instance, the line separator itself is a return carriage or a line break.

Be the first to comment

Leave a Reply

Your email address will not be published.


*