Just discovered this but where is it documented so...

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

  • Nevermind just found

    Topic is

    COLLATE

    and

    Collation Precedence (example found here)

    You can use several other places.

  • Oops, should include SQL Server 2000, not 7.

  • 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

     

     

  • 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