SQL Server 2016: New useful function STRING_SPLIT()

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.