The STRING_SPLIT Function in SQL Server

A long-awaited string function added to SQL Server 2016 (and later) the is STRING_SPLIT function. Read on to better understand this cool function and see examples of how you can use it.


What does the STRING_SPLIT Function in SQL Server do?

As the name implies: this function splits the given character expression using the separator set by user.


Examples of using the STRING_SPLIT function in SQL Server

Let’s see some examples of using the STRING_SPLIT function.


Example 1

--Example #1
DECLARE @string AS VARCHAR(250);
SET @string = '1-2-3-4-5-6-7-8-9-10';

FROM STRING_SPLIT(@string, ',');

Here’s the output of the above T-SQL script:


Example 2

--Example #2
      id INT ,
      productsPurchased VARCHAR(250)

VALUES  ( 1, 'product1, product2, product3' );

SELECT  id ,
FROM    #test t
        CROSS APPLY STRING_SPLIT(productsPurchased, ',') t2;


Here’s the output of the above T-SQL script:


Example 3

--Example #3
CREATE TABLE #CSVHeaders ( headers VARCHAR(MAX) );

VALUES  ( 'header1,header2,header3,header4,header5,header6,header7,header8,header9,header10,header11,header12,header13,header14,header15' );

SELECT  LTRIM(value) AS HeaderName
FROM    #CSVHeaders
        CROSS APPLY STRING_SPLIT(headers, ','); 


Here’s the output of the above T-SQL script:

The STRING_SPLIT Function in SQL Server - Article on SQLNetHub



Concluding Remarks

As you can see from the above examples, STRING_SPLIT is a very handy new string function in SQL Server 2016 and later. It allows you to easily manipulate text and organize it by parsing it using separators.


Learn essential SQL Server development tips! Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers(special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access, Certificate of Completion, downloadable resources and more!)

Learn More

Learn More


Featured Database Productivity Tools

Develop T-SQL code faster with Snippets Generator: Create and modify T-SQL snippets for use in SQL Management Studio, fast, easy and efficiently.

Snippets Generator - SQL Snippets Creation Tool

Learn more


Dynamic SQL Generator: Convert static T-SQL code to dynamic and vice versa, easily and fast.

Dynamic SQL Generator: Easily convert static SQL Server T-SQL scripts to dynamic and vice versa.

Learn more


Subscribe to our newsletter and stay up to date!

Check our online courses!

Check out our eBooks!


Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)


Reference: SQLNetHub (

© SQLNetHub