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.