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))
FROM customers (NOLOCK)
WHERE CONVERT(varbinary,UPPER(LEFT(CompanyName,4))) = CONVERT(varbinary,LEFT(CompanyName,4))
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.
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 NorthwindSELECT CustomerID, CompanyName, ContactName, ContactTitle, City FROM customers (NOLOCK) WHERE UPPER(LEFT(CompanyName,4)) = LEFT(CompanyName,4) COLLATE Latin1_General_cs_aiCourse, 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
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