PROBLEM

When you work on SQL Server Editions that are prior to SQL Server 2016, it is very difficult for us to split a string with a specified separator such as comma,… You must build your own table-value function to split the string.

SOLUTION

However, we have a built-in function that named STRING_SPLIT function which is one of the most awaiting feature for a long time. This function has been introduced by Microsoft in SQL Server 2016

SYNTAX

STRING_SPLIT function can be used to parse a string with a specified separator. The following syntax as below

STRING_SPLIT(<string>,’<separator>‘)

<string>: is a character string that you need to split and it can be nvarchar, varchar, nchar, char

<separator>: is a single character string and it can be nvarchar(1), varchar(1), nchar(1) and char(1)

The function returns a table that has only a single column named value. Data type of this column depends on data type of the input arguments (<string>,’<separator>‘). If one of them is Unicode then data type will be nvarchar. Otherwise, it will be varchar (Non-Unicode).

Besides, the length of the column is equal the length of <string> input argument

EXAMPLE

Running the below code

USE tempdb;
GO
SELECT * FROM STRING_SPLIT('1,2,3,4,5,6,7',',')

Here is the result

Example of STRING_SPLIT

 

LIMITATIONS

STRING_SPLIT is very useful and help us split a simple string quickly but it has some limitations

  1. Currently, this function only supports a single character string and even it only supports a comma character. Let’s try to use a vertical bar (|) and see the output.
    USE tempdb; 
    GO 
    SELECT * FROM STRING_SPLIT('1,2,3,4,5,6,7','|')

    STRING_SPLIT limitation

  2. This function is used in any database that it’s compatibility level is 130.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s