At some point in your life, you’ve probably had to parse a string inside SQL Server. Maybe you had a comma delimited string and you need the data inside a table. So, you go to stackoverflow and find some stored procedure that someone made that you don’t understand. It works, but yuck. Well… those days are over! In SQL 20016 they introduced a new table-valued function called STRING_SPLIT. This makes is very easy to parse a comma delimited string in SQL Server.
STRING_SPLIT Basics
The STRING_SPLIT function is really easy to use. It basically just takes 2 parameters. The first parameter is the string that you want to parse. The second parameter is the delimiter for parsing on.
The STRING_SPLIT function will always return a one column result set with a column named value.
Here’s an example of how to use it:
SELECT value AS AnimalName
FROM STRING_SPLIT('Dog,Cat,Bird,Walrus', ',')
[table width=”150″ colwidth=”150″ colalign=”left”]
value
Dog
Cat
Bird
Walrus
[/table]
If your input string has spaces by the delimiter, you just need to trim the value that you get from the function. Here’s an example of trimming the spaces and also parsing on a pipe character:
SELECT value AS AnimalName
FROM STRING_SPLIT('Dog |Cat |Bird | Walrus', '|')
Source: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql