Post reply

How do I make a case-sensitive query?

  • May 6, 2004 at 9:48 am

    #107660

    Hi, I have a db set up with a Case-insensitive Sort Order.  I'd like to perform a query that will differentiate between upper case and lower case, e.g. 'Y' <> 'y'.

    Example:

    SELECT l_use, COUNT(l_use) countlu FROM #t
    GROUP BY l_use

    Current result:

    l_use   countlu
    -----   -------
    y         45
    n         50

    But I want:

    l_use   countlu
    -----   -------
    y         40
    Y         5
    n         25
    N         25

    Is there some keyword or option that will allow this for an individual query?  My actual query has a lot more than just upper and lower case y and n, or I would probably use ASCII() function to do the comparison...

    t.i.a.

    Greg.

  • May 6, 2004 at 9:56 am

    #505510

    SELECT l_use, COUNT(l_use) countlu FROM #t

    GROUP BY CAST(l_use as varbinary),l_use

    Far away is close at hand in the images of elsewhere.
    Anon.

  • May 6, 2004 at 10:03 am

    #505511

    Thanks David!  I figured there had to be an easy way.

  • May 6, 2004 at 1:23 pm

    #505545

    Greg another way would be to use the COLLATE clause on your field.

    SELECT I_use COLLATE Latin1_General_Bin

        , COUNT(I_use COLLATE Latin1_General_Bin)

    FROM #t

    OR Better yet when you create your temp table just put the collation on the field. Then when you insert the data make sure to use the COLLATE if you are changing the collation.

    IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id('tempdb..#Test'))

        DROP TABLE #Test

    CREATE TABLE #Test

        (

        id int identity(1,1)

        ,I_use nvarchar(50) COLLATE Latin1_General_Bin

        )

    INSERT INTO #Test(I_use) VALUES('a')

    INSERT INTO #Test(I_use) VALUES('B')

    INSERT INTO #Test(I_use) VALUES('A')

    INSERT INTO #Test(I_use) VALUES('b')

    SELECT I_use, COUNT(I_use)

    FROM #Test

    GROUP BY I_use

     

     

     




    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.

  • May 11, 2004 at 8:03 am

    #505973

    Thanks Gary, That looks like another good way.  Do I assume correctly that by using Collate I'm over-riding the default database sort order?  Is it required to use nvarchar instead of varchar in this case?

    Whoops, I just looked at BOL, COLLATE will have to wait until I'm on SQL 2000, which will probably happen this year (hopefully...)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply