How do I make a case-sensitive query?

Question

Post reply

How do I make a case-sensitive query?

GregLyon

SSCommitted

Points: 1946
More actions
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.

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

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

David Burrows SSC Guru Points: 65144 More actions · Answer 1

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.

GregLyon SSCommitted Points: 1946 More actions · Answer 2

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

gljjr SSChampion Points: 10080 More actions · Answer 3

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.

GregLyon SSCommitted Points: 1946 More actions · Answer 4

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