The binary datatype of SQL Server is one of those features most developers don’t really use that often, but it turns out there’s more to binary values than just storing large, non-relational blobs.
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.