SQL Server STRING_SPLIT Function

Summary: in this tutorial, you will learn how to use the SQL Server STRING_SPLIT() function to split a string into a row of substrings based on a specified separator.

Introduction to SQL Server STRING_SPLIT() function

The STRING_SPLIT() function is a table-valued function that splits a string into a table that consists of rows of substrings based on a specified separator.

The following shows the syntax of the STRING_SPLIT() function:

STRING_SPLIT ( input_string , separator )  
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• input_string is a character-based expression that evaluates to a string of NVARCHAR, VARCHAR, NCHAR, or CHAR.
• separator is a single character used as a separator for splitting.

The STRING_SPLIT() function returns a single-column table, whose column name is value. This result table contains rows which are the substrings.

Note that the values in the value column can be in any sort order. To get the expected order, you need to add the ORDER BY clause to the SELECT statement:

ORDER BY value [ASC|DESC]
Code language: SQL (Structured Query Language) (sql)

SQL Server STRING_SPLIT() function examples

Let’s take some examples of using the STRING_SPLIT() function.

A) Using the STRING_SPLIT() function to split comma-separated value string

This example uses the STRING_SPLIT() function to parse a comma-separated list of values:

SELECT 
    value  
FROM 
    STRING_SPLIT('red,green,,blue', ',');
Code language: SQL (Structured Query Language) (sql)

Here is the output:

value
---------------
red
green

blue

(4 rows affected)

The third row is empty because the input string contains two consecutive commas (,,). To get non-empty substrings, you add a WHERE clause to the SELECT statement as shown in the following query:

SELECT 
    value  
FROM 
    STRING_SPLIT('red,green,,blue', ',')
WHERE
    TRIM(value) <> '';
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

value
---------------
red
green
blue

(3 rows affected)

B) Using STRING_SPLIT() function to split a comma-separated string in a column

Sometimes, database tables are not normalized. A typical example of this is when a column can store multiple values separated by a comma (,).

The STRING_SPLIT() can help normalize the data by splitting these multi-valued columns.

Let’s set up a sample table for the demonstration.

First, create a new table named sales.contacts that stores contact information:

CREATE TABLE sales.contacts (
    id INT PRIMARY KEY IDENTITY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phones VARCHAR(500)
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some contacts into the sales.contacts table:

INSERT INTO 
    sales.contacts(first_name, last_name, phones)
VALUES
    ('John','Doe','(408)-123-3456,(408)-123-3457'),
    ('Jane','Doe','(408)-987-4321,(408)-987-4322,(408)-987-4323');
Code language: SQL (Structured Query Language) (sql)

Third, use the STRING_SPLIT() function to split the phone numbers and CROSS APPLY to join with the sales.contacts table:

SELECT 
    first_name, 
    last_name,
    value phone
FROM 
    sales.contacts
    CROSS APPLY STRING_SPLIT(phones, ',');
Code language: SQL (Structured Query Language) (sql)

Here is the output:

C) Using the STRING_SPLIT() function with an aggregate function

The following example returns the contacts and the number of phones for each contact:

SELECT 
    CONCAT_WS(' ',first_name,last_name) full_name,
    COUNT(value) number_of_phones
FROM 
    sales.contacts
    CROSS APPLY STRING_SPLIT(phones, ',')
GROUP BY 
    CONCAT_WS(' ',first_name,last_name);
Code language: SQL (Structured Query Language) (sql)

The output is shown as follows:

In this tutorial, you have learned how to use the STRING_SPLIT() function to split strings into a table of substrings by a specified separator.