Splitting a range string into a table

This week’s post is a requirement that I see very regularly as a developer. You get a plaintext string containing one or more ranges. Each range is comma delimited, and the start and end values of the range are separated by a dash. The string could look something like this, for example: 100-120,121-499,510,520,790-999.

Wouldn’t it be practical if we could construct a table value function that returns one row for each range, with columns for the start and end of each range?

Collations and case sensitive wildcards

The other day, I ran across the following issue: I was looking to split a value using upper case and lower case characters. Here’s an example query:

SELECT PATINDEX('%[a-z][A-Z]%',
    'testValue' COLLATE Finnish_Swedish_CS_AS) AS offset

I expected this query to return the value 4, because at that offset in the string, there is a lower case character (t) followed directly by an upper case character (V). However, in practice, this query returns the value 1.