MS SQL Server STRING_SPLIT() Function

Sometimes you may need to split a tags value of column level  into row level  in MS SQL Server. There is a function called STRING_SPLIT in MS SQL Server by which you can do this. STRING_SPLIT function is table-valued function that splits a string into rows of substrings, based on a specified separator character.

Let’s see the function syntax:

STRING_SPLIT(string_expression,separator);

In this syntax:

• In this function there are two parameters: one is string_expression and another is separator.
• Parameter string_expression is an expression of any character type (for example, nvarchar, varchar, nchar, or char).
• Parameter string_expression can be a constant, variable, or column.
• Parameter separator is a single character expression of any character type (for example, nvarchar(1), varchar(1), nchar(1), or char(1)) that is used as separator for concatenated substrings.
• Function returns a single-column table whose rows are the substrings. The name of the column is value. Returns nvarchar if any of the input arguments are either nvarchar or nchar. Otherwise returns varchar. The length of the return type is the same as the length of the string argument.

The following example uses STRING_SPLIT function to convert an integer  value to an Unicode character.

SELECT value
FROM STRING_SPLIT('Dhaka Dacca DHK Dhaak haka',' ')
WHERE TRIM(value)<> ''; 

Here is the result set.

tbld_districts table has a column with comma-separate list of tags shown in the following example:

Following query transforms each list of tags and joins them with the original row:

SELECT id,name,value
FROM [DataAnalytics].[dbo].[tbld_districts]
CROSS APPLY STRING_SPLIT(tags,',');

I have an another post MS SQL Server String Related Functions where you will learn about different string related functions of MS SQL Server.

In this tutorial, I have shown you how to use STRING_SPLIT function in MS SQL Server. Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.