Case sensitivity - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Case sensitivity

Author

Topic

ekareem
Starting Member

9 Posts

Posted - 2006-04-16 : 07:13:43

When I do
select * from mytable where somecolumn='a'

I get rows for data with the value 'a' and rows for data with value 'A'

How to control cases sensitivity?
Thanks.

Kristen
Test

22859 Posts

Posted - 2006-04-16 : 07:21:51

select * from mytable where somecolumn='a' COLLATE Latin1_General_BIN

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-16 : 09:38:52

You might want to try this if there is an index on somecolumn

select * from mytable
where somecolumn='a' COLLATE Latin1_General_BIN
and somecolumn='a'

The collate will probably stop it using the index so the extra condition will cause it to get the case insensitive rows then do the case sensitive check on only those that match.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 02:07:03

Will casting to varbinary make use of index?

select * from mytable
where cast(somecolumn as varbinary(100))=cast('a' as varbinary(100))

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-04-17 : 06:41:13

If you want the column to be case sensitive then use ALTER TABLE to change the COLLATE on that column?

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-17 : 09:40:47

quote:
Originally posted by madhivanan

Will casting to varbinary make use of index?

select * from mytable
where cast(somecolumn as varbinary(100))=cast('a' as varbinary(100))

Madhivanan

Failing to plan is Planning to fail

Nope

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources