SQL: Compare case in WHERE clause


Here’s one of those really easy SQL tips that I always forget (in fact I’m just posting it here for ease of finding later)
In VFP it is easy to compare case ie
SELECT fields FROM mytable
   WHERE UPPER(field) == field
If you want to compare case in a SQL Server database that is case insensitive, just convert to varbinary first eg:
SELECT fields FROM mytable
   WHERE CONVERT(varbinary, UPPER(field)) = CONVERT(varbinary, field)
Here’s an example:
USE Northwind
SELECT CustomerID, CompanyName, ContactName, ContactTitle, City
 FROM customers (NOLOCK)
 WHERE CONVERT(varbinary,UPPER(LEFT(CompanyName,4))) = CONVERT(varbinary,LEFT(CompanyName,4))