T-SQL 101: 51 Splitting delimited strings in SQL Server by using STRING_SPLIT

For a long, long time,  users of SQL Server had requested some way to split a string. That’s a common need when working with rows from comma-delimited files (CSVs).

In the example below, I’ve asked it to break up the string ‘Greg,Tom’,Sandra’ whenever it finds a comma. Notice I could use another delimiter like TAB or semicolon instead.

The values returned are in a table. This is a table-valued function.

The downside of this function is that it doesn’t promise to return the rows in order (as tables don’t have a default order), and it doesn’t return a column that lets us know what order the columns were in. So it can be a bit awkward to work with.

If you want a function that works better than this, in our free SDU Tools for Developers and DBAs, we have a SplitStringByDelimiter function that does just that. I suggest you take a look at it.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2020-01-06