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))
Tweet FROM customers (NOLOCK)
WHERE CONVERT(varbinary,UPPER(LEFT(CompanyName,4))) = CONVERT(varbinary,LEFT(CompanyName,4))
{ 2 comments… read them below or add one }
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