case sensitive WHERE clause possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

case sensitive WHERE clause possible?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-29 : 05:43:01

Hi,

I am wondering, is it possible to run a case sensitive WHERE clause? I just want to run it case sensitive on one particular query, due to some problems in the front end application.

Any suggestions greatly appreciated !

Thanks again,
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 05:48:37

yup. you can. just use a case sensitive collation during comparison
or convert it to binary. refer link below for details

http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-29 : 05:59:46

Hi Visakh16,

Got it going, with the statement shown below. great article thank you!

To push things a step further, do you think it would be possible to create a query with a groupBy on the case sensitive WHERE statement? I cant really figure out how I would take this query to the next level. If you have any suggestions I would love your input.

Thanks once again! Your help is greatly appreciated :)

mike123

Next Step Desired Query Results:

cityName userCount

cityname 5000
CITYNAME 100
CiTyNaMe 20
CITYname 10
cityNAME 5

Current working query:

DECLARE @city varchar(50)
SET @city = 'cityName'

select count(*) as totalUsers from tbluserdetails where city = CAST(@city AS varbinary(50)) AND CAST(city AS varbinary(50)) = CAST(@city AS varbinary(50))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 06:04:03

COLLATE?

DECLARE	@Sample TABLE
	(
		cityName VARCHAR(20)
	)

INSERT	@Sample
SELECT	'cityname' UNION ALL
SELECT	'CITYNAME' UNION ALL
SELECT	'CiTyNaMe' UNION ALL
SELECT	'cityNAME' UNION ALL
SELECT	'cityname' UNION ALL
SELECT	'CITYname' UNION ALL
SELECT	'CiTyNaMe' UNION ALL
SELECT	'cityNAME'

SELECT		cityName COLLATE Latin1_General_BIN,
		COUNT(*) as totalUsers
FROM		@Sample
GROUP BY	cityName COLLATE Latin1_General_BIN

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-29 : 06:20:37

quote:

Originally posted by Peso

COLLATE?

DECLARE	@Sample TABLE
	(
		cityName VARCHAR(20)
	)

INSERT	@Sample
SELECT	'cityname' UNION ALL
SELECT	'CITYNAME' UNION ALL
SELECT	'CiTyNaMe' UNION ALL
SELECT	'cityNAME' UNION ALL
SELECT	'cityname' UNION ALL
SELECT	'CITYname' UNION ALL
SELECT	'CiTyNaMe' UNION ALL
SELECT	'cityNAME'

SELECT		cityName COLLATE Latin1_General_BIN,
		COUNT(*) as totalUsers
FROM		@Sample
GROUP BY	cityName COLLATE Latin1_General_BIN

E 12°55'05.63"
N 56°04'39.26"

works perfectly!! .. thank you once again Peso! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources