Microsoft SQL Server 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:

  1. string. Any character type expression (char, nchar, varchar or nvarchar)
  2. seperator. A single character expression of any type (char(1), nchar(1), varchar(1) or nvarchar(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_SPLIT function 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 execute STRING_SPLIT function. 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.