The STRING_SPLIT Function in SQL Server

The STRING_SPLIT Function in SQL Server

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

SELECT  Value
FROM STRING_SPLIT(@string, ',');
GO

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

The STRING_SPLIT Function in SQL Server - Article on SQLNetHub

 

Example 2

--
--Example #2
--
CREATE TABLE #test
    (
      id INT ,
      productsPurchased VARCHAR(250)
    );

INSERT  INTO #test
VALUES  ( 1, 'product1, product2, product3' );

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

 

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

The STRING_SPLIT Function in SQL Server - Article on SQLNetHub

 

Example 3

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

INSERT  INTO #CSVHeaders
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, ','); 
GO

 

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

The STRING_SPLIT Function in SQL Server - Article on SQLNetHub

 

Conclusions

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 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 newsletterand stay up to date!

Check our latest software releases!

Easily generate SQL code snippets with Snippets Generator!

Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.

Secure your SQL Server instances with DBA Security Advisor.

Benchmark SQL Server memory-optimized tables with In-Memory OLTP Simulator.

Check out Artemakis’s eBooks!

 

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

Loading...

Reference: SQLNetHub (https://www.sqlnethub.com)

© SQLNetHub

Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.