Microsoft SQL Server String Functions String_Split
Example
SQL Server 2016
Splits a string expression using a character separator. Note that STRING_SPLIT() is a table-valued function and therefore must be used within FROM clause.
Parameters:
- string. Any character type expression (
char,nchar,varcharornvarchar) - seperator. A single character expression of any type (
char(1),nchar(1),varchar(1)ornvarchar(1)).
Returns a single column table where each row contains a fragment of the string. The name of the columns is value, and the datatype is nvarchar if any of the parameters is either nchar or nvarchar, otherwise varchar.
The following example splits a string using space as a separator:
SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');
Result:
value
-----
Lorem
ipsum
dolor
sit
amet.
Remarks:
The
STRING_SPLITfunction is available only under compatibility level 130. If your database compatibility level is lower than 130, SQL Server will not be able to find and executeSTRING_SPLITfunction. You can change the compatibility level of a database using the following command:
ALTER DATABASE [database_name] SET COMPATIBILITY_LEVEL = 130
SQL Server 2016
Older versions of sql server does not have a built in split string function. There are many user defined functions that handles the problem of splitting a string. You can read Aaron Bertrand's article Split strings the right way – or the next best way for a comprehensive comparison of some of them.
