SQL Server Performance

Case sensitive query against a case-insensitive DB

Discussion in 'General Developer Questions' started by MitchG, Aug 21, 2003.

  1. MitchG New Member

    My SQL Server 2000 was installed with the defaults, and is case-insensitive. Let's say I have a column called NAME and two of the rows for NAME are populated with:

    SMITH
    Smith

    If I query:
    SELECT NAME FROM TABLE WHERE NAME='SMITH'
    ...I'd get both rows as hits, because the database is case-insensitive.

    Is there a way I can force the query to be case-sensitive? I only want to get "SMITH" returned and not "Smith". Maybe there's some string function I don't know about that does an exact character-for-character comparison?

    Thanks,
    --Mitch
  2. satya Moderator

    If you are unsure about whether the database uses a case-sensitive search, you can use the UPPER or LOWER functions in the search condition to convert the case of the search data, as illustrated in the following example:

    WHERE UPPER(lname) = 'SMITH'


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. gaurav_bindlish New Member

    Convert the values - Column data and data to be compared into binary format using CONVERT or CAST function and then compare the values.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  4. MitchG New Member

    Satya,

    Thanks. I know the database is case-insensitive. Unfortunately, some data stored there is case-sensitive, and I want to refine my query so that it find based on exactly what I put in the WHERE clause.

    I just tried your suggestion and it doesn't work. If you run it, you'll get both rows back. Also, it doesn't help me if I wanted to query for "Smith" but not "SMITH".
  5. MitchG New Member

    Guarav,

    Thanks. That put me on the right track. Here's the query:

    select NAME from TABLE where CAST(RTRIM(NAME) AS VARBINARY)=CAST(RTRIM('SMITH ') AS VARBINARY)

    --Mitch
  6. SQL_Guess New Member

    Can you use this following concept, extrapolated (from BOL) <br /><br />Consider that the ascii value for the data in the db will be different.<br /><br />WHERE ascii(substring(NAME,1,1)) = 84 -- uppercase S<br />WHERE ascii(substring(NAME,1,1)) = 115 -- lowercase s<br /><br />so, (UGLY) for example<br /><br />set @colval = 0<br />set @position = 0<br />while @positon &lt; len(name)<br /> begin <br /> @colval = @colval + ASCII(SUBSTRING(@string, @position, 1))<br /> @positon = @positon +1<br />end<br /><br />then get the val of Smith and smith - they will be different<br /><br />Honestly, I can't really think of the structure, it just the idea that the sum of the ascii values for the 2 data items will be different.<br /><br />Since the clever guys will HATE what I've posted, they may well come up with a good way [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  7. SQL_Guess New Member

    oops- I see a clever guys already beat me to it...

Share This Page