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.
After doing some googling, the problem breaks down to how different collations work in wildcards using intervals (such as a-z or A-Z). Here’s a more specific test to prove what happens:
SELECT (CASE WHEN 'm' COLLATE Finnish_Swedish_CS_AS LIKE '[A-Z]' THEN 'Yes' ELSE 'No' END);
This query will return “yes”, even though m is not an upper case character. The following query, however, will not:
SELECT (CASE WHEN 'm' COLLATE Finnish_Swedish_CS_AS LIKE '[ABCDEFGHIJKLMNOPQRSTUVXYZ]' THEN 'Yes' ELSE 'No' END);
So what’s the difference? [A-Z] is not the same thing as [ABCD….XYZ] if you’re using a case sensitive collation order. Sure, the collation order differentiates between upper case and lower case characters, but it sorts them as a, A, b, B, c, C, …, z, Z. And the sort order is how wildcard intervals like [A-Z] work.
Sorting order with different types of collations
Here’s a practical overview of how different collations sort character values:
WITH characters (ch) AS ( SELECT 'a' UNION ALL SELECT 'A' UNION ALL SELECT 'b' UNION ALL SELECT 'B' UNION ALL SELECT 'c' UNION ALL SELECT 'C') SELECT ch, ASCII(ch) AS [ascii], DENSE_RANK() OVER (ORDER BY ch COLLATE Finnish_Swedish_CI_AS) AS ci, DENSE_RANK() OVER (ORDER BY ch COLLATE Finnish_Swedish_CS_AS) AS cs, DENSE_RANK() OVER (ORDER BY ch COLLATE Finnish_Swedish_BIN) AS bin FROM characters ORDER BY 2;
Note how the case sensitive sort order will order the characters a, A, b, B, c, C, wheras the binary sort order will order the characters by their ASCII codes, A, B, C, a, b, c.
The solution to the problem is to use a binary sort order when using [A-Z] and [a-z] wildcards.
SELECT PATINDEX('%[a-z][A-Z]%', 'testValue' COLLATE Finnish_Swedish_BIN) AS offset
Now, the query will return the value 4.