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.
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
STRING_SPLIT function can be used to parse a string with a specified separator. The following syntax as below
<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
Running the below code
USE tempdb; GO SELECT * FROM STRING_SPLIT('1,2,3,4,5,6,7',',')
Here is the result
STRING_SPLIT is very useful and help us split a simple string quickly but it has some limitations
- 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','|')
- This function is used in any database that it’s compatibility level is 130.