Use T-SQL in SQL Server to Split a Delimited String or Break a Range of Numbers into Rows

Splitting delimited strings and turning a range of numbers (e.g. 1-12 or 349-450 ) into individual numbers in result set rows are common needs in applications.

There are a number of ways to split delimited strings in T-SQL and/or SQL Server.  The scenario is you might have a table with data stored in a format such as “1,4,67,23” and you need those values broken out into multiple rows to deal with each individually. Solutions range from native T-SQL to CLR or XML.

For delimited splitting, check out these great links to decide what approach is best for you:

1) An Improved SQL CSV Splitter Function

2) SQL Server CLR and SQL split functions to parse a delimited string

3) Split strings the right way – or the next best way

And for a nice approach to “de-ranging” a range of numbers check out: Splitting a range string into a table