SQL: Compare case in WHERE clause

S

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))

4 comments

About Craig

I'm the co-host of HubShots and the CEO of XEN - helping mid-large B2B companies with their digital marketing and lead generation.

Craig Bailey

Archives

Posts