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