Now, in the latest version of SQL Server, you have one of the most expected function as well as for developers as for administrators, splitting a string natively in T-SQL:

STRING_SPLIT(<character expression>,<separator>)

This function has 2 parameters:

  • The character expression with a data type of nvarchar,varchar,nchar or char
  • The separator with a data type of nvarchar(1), varchar(1), nchar(1) or char(1)

The function return a table of one column with all splitting string

A first test with a text of mine on the dbi-services website and the ‘.’ as separator:

If I use more than one character for the separator like ‘. ’, an error message appears:
Msg 214, Level 16, State 11, Line 1
Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.


This function is very useful if you have a list like the countries beginning with A:
Another usage is for a folder path:
This is a nice, easy and useful function but limited to one character as separator for the moment…
Msdn link to the function here.