SQL: Compare case in WHERE clause

Posted on November 26, 2005. Filed under: Visual FoxPro |

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

Make a Comment

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

2 Responses to “SQL: Compare case in WHERE clause”

RSS Feed for Craig Bailey on Microsoft Comments RSS Feed

Or, you could just use a case sensitive collation, and then you may be able to continue to use your indexes (not in the axample below admittedly).

e.g.
USE Northwind

SELECT CustomerID, CompanyName, ContactName, ContactTitle, City
FROM customers (NOLOCK)
WHERE UPPER(LEFT(CompanyName,4)) = LEFT(CompanyName,4) COLLATE Latin1_General_cs_ai

Course, that assumes you’re using SQL 2000. It won’t work on SQL 7.

Owl.

I just came across this after posting about this same thing last week on my blog. Interesting alternatives…..

http://randyjean.blogspot.com/2005/12/how-to-force-sql-server-query-to-be.html


Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: