We’ve discussed earlier on the effect of using SQL wildcards, such as ‘%[a-z]%’ with different collation orders, particularly case sensitive ones. Here’s another take on collations, and how different collation orders can sort text differently.
collation
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.
sqlsunday.com 