Microsoft SQL Server Case Insensitivity & Work Arounds

Until recently (about 6 months ago, I’d forgot about it till it cropped up again yesterday) i’d always assumed when writing SQL for Microsoft SQL Server that the EQUALS operator was case sensitive, and the LIKE operator insensitive.

For example, if i had a user table with a name that was stored as ‘Carl’ i assumed

SELECT * FROM [User] WHERE [name] = 'carl'

Would return no results due to the casing been wrong and

SELECT * FROM [User] WHERE [name] LIKE 'carl'

Would return the user, as it would ignore the case.

Turns out, in a default setup of SQL Server that is not the case at all; they would both return the user as the EQUALS operator also ignores casing, hmm!

There are several workarounds for this issue, firstly you can change the collation settings when setting up your database server in the first place; but i’m guessing if your reading this its probably too late for this. Don’t despair though, you can change your collation settings in SQL Server 2005+ after setup if you want to go from case insensitive to case sensitive but not vice versa. This however is not my recommended cause of action, so i won’t supply detailed instructions – just google how to change the collation settings.

Recommended Solution

Lets be honest, to me it seems the reason Microsoft has done this in the first place is to save us from having to ‘LOWER() ‘ all the comparisons in our queries and in the process possibly make them more efficient! and in most cases are we really fussed about case insensitivity in SQL? probably not. However there are times when its needed; for instance I said this had been drawn to my attention again yesterday, that’s because I was writing a SQL function to check if a voucher code was valid; a good case for case sensitivity I reckon! (see what I did there) ; the good news is, we can do it on a per SQL statement (and even column) basis! all thanks to the COLLATE keyword! huzzah; alright enough talking here’s the code already; i’ll work on our example from earlier.

SELECT *
FROM [User]
WHERE [name] COLLATE SQL_Latin1_General_CP1_CS_AS = 'carl'

This would now return no rows! as its made the query on the ‘name’ column case sensitive! another way of achieving this effect is converting them to VARBINARY types, like so

SELECT *
FROM [User]
WHERE CONVERT(VARBINARY, [name]) = CONVERT(VARBINARY,'carl')

Personally i prefer the collation method as it saves on the explicit converts!
Anyhow, hope this helps! happy SQL weirdisms to you all.

Comments

comments powered by Disqus