January 27, 2005 at 10:31 am
Not that it is a big woohoo or anything but I just found that you can do this
SELECT * FROM FinCenters
WHERE FC_NAME LIKE 'south%' COLLATE SQL_Latin1_General_Cp850_CS_AS
ORDER BY FC_NAME
SELECT * FROM FinCenters
WHERE FC_NAME LIKE 'south%' COLLATE SQL_Latin1_General_Cp850_CI_AS
ORDER BY FC_NAME
And if the FC_NAME field doesn't start Case Sensitive in the first case it will not return the row, but in the second because I use Case Insenstive it will return all. You can also use on the order by clause. I ask because I cannot find where this fact is documented and I know there have been questions about doing Case Senstive on Case Insenstive systems and vice versa.
So can anyone point me to the right documentation (I need topic in BOL if you send me there but I cannot find in the most current version).
January 27, 2005 at 10:42 am
Nevermind just found
Topic is
COLLATE
and
Collation Precedence (example found here)
You can use several other places.
January 27, 2005 at 10:46 am
Oops, should include SQL Server 2000, not 7.
January 28, 2005 at 3:23 pm
Another (basic) approach that I like to use is to force the case on both sides of the equation. Not rocket-science or anything, but it works:
SELECT * FROM FinCenters
WHERE UPPER(FC_NAME) LIKE UPPER('south%')
ORDER BY FC_NAME
January 28, 2005 at 3:56 pm
I use COLLATE all the time as we use the Latin1_General_Bin collation as the default collation on our database. So if I need to compare case insensitive data I need to use the collation clause on the where clause. However I would usually put the COLLATE on the field and not the literal string.
SELECT *
FROM FinCenters
WHERE FC_NAME COLLATE Latin1_General_CI_AS LIKE ('south%')
As John said the UPPER and LOWER functions will also do this however I have found that the COLLATE seems to have slightly better performance.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply