Split Text with Separator in lines with SQL Server STRING

The functionality to split text into different lines based on a separator is available natively from SQL Server 2016 and later.

In order to split text with separator into lines, I have been using T-SQL user-defined functions for years before the SQL Server 2016 version came out. Indeed, starting in SQL Server 2016 a new built-in function allows this feature. It’s the T-SQL STRING_SPLIT function.

Table of Contents

1. Simple comma-separated values using STRING_SPLIT

For this first example, a common use case for STRING_SPLIT is to split a string of comma-separated values (CSV) into individual rows.

SELECT value
FROM STRING_SPLIT('Apple,Orange,Banana,Grapes,Mango', ','); 

-- Use the second example to rename the column
SELECT value as Fruit
FROM STRING_SPLIT('Apple,Orange,Banana,Grapes,Mango', ',');

*Split Text with Separator into Lines with SQL Server STRING_SPLIT function*

2. Parse text with semi-colon separator into lines with SQL Server

Another practical application is splitting a string of emails separated by semi-colons into individual rows.

SELECT value as [Email]
FROM STRING_SPLIT('example1@email.com;example2@email.com;example3@email.com', ';')

This will return a table where each row contains a single email address from the original string.

3. Split text from variable into multiples rows

So, this third example, we use this time cities. The goal is to achieve the same result but using variables to store the string and the separator. The code is more flexible and could handle data coming from a table for example.

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

About the T-SQL STRING_SPLIT Function

The STRING_SPLIT function in SQL Server is a powerful tool for text manipulation, enabling efficient splitting of strings based on a specified separator. Whether it’s handling CSV data or parsing complex strings, STRING_SPLIT provides a straightforward solution for converting delimited text into separate lines. Its simplicity and effectiveness make it an essential feature for developers and database administrators dealing with text processing and data transformation tasks.

Split a string with delimiters into multiple 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 another tutorial with an example of how to select each value from the @String variable into separate dedicated column.

Full tutorial on how to Split delimited text into columns in SQL Server