How to split a long SQL Server text into lines?

How to split a long SQL Server text into lines? 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 you chose. 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.

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”

How to split a SQL Server text into lines?

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, but this time 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 the line breaks from a SQL Server string in order to store it in a single line of a table.

Be the first to comment

Leave a Reply