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.

Examples of using the STRING_SPLIT function in SQL Server

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

--
--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:

--
--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:

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:

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.

Featured Online Courses:

• SQL Server 2022: What’s New – New and Enhanced Features
• Working with Python on Windows and SQL Server Databases
• Introduction to Azure Database for MySQL
• Boost SQL Server Database Performance with In-Memory OLTP
• Introduction to Azure SQL Database for Beginners
• Essential SQL Server Administration Tips
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Development Tips for SQL Developers
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
  
• SQL Server 2019: What’s New – New and Enhanced Features
• Entity Framework: Getting Started – Complete Beginners Guide
  
• Data Management for Beginners – Main Principles
  
• A Guide on How to Start and Monetize a Successful Blog

Check Also:

• The set identity_insert Command in SQL Server
• The Import Flat File Wizard in SSMS v17.3
• Listing all Tables of a Linked Server’s Database
• How to Import and Export Unstructured Data in SQL Server – FileTables
• How to Import and Export Unstructured Data in SQL Server – FILESTREAM
• How to Import and Export Unstructured Data in SQL Server – The IMAGE Datatype
• …more

Subscribe to our newsletter and stay up to date!

Check our online courses!

Check out our eBooks!

Rate this article: (2 votes, average: 5.00 out of 5)

Loading...

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

© SQLNetHub