Splitting Strings in SQL Server 2016

   

     SQL Server has a new built-in function to split strings using a specified separator. To be able to use this function, your database compatibility level has to be at least 130. If you need to adjust the compatibility level, you use the following command.

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

If your production database has a lower compatibility level, It's always a good idea to ask to your DBA if it is okay to adjust compatibility level before you write any queries using this function.

STRING_SPLIT(string, separator)

string is any character type value.

separator is a single character expression of any character type

STRING_SPLIT takes a string and uses the separator character to divide it into multiple rows. You need to use the "value" in your select statement to get the results. You can call the value anything else if you like.