Case sensitive comparison in SQL Server 2000

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 2000 Forums

SQL Server Development (2000)

Case sensitive comparison in SQL Server 2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-29 : 08:17:39

Gopal Iyer writes "We are wanting to do a case-sensitive comparison for an entire string in SQL Server 2000. We did find the UNICODE function but that wasn't much help when the string has more than one character. Could you help us with a way where an n-length string can be compared against another m-length string, where m>0, n>0 ? Thanks for your time."

dsdeming

479 Posts

Posted - 2002-07-29 : 08:34:09

You need to convert the strings to varbinary to do a case-sensitive search. CONVERT( varbinary( x ), columna ) = CONVERT( varbinary( x ), columnb ) where x is the length of the string.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-29 : 08:36:13

Use COLLATION

e.g.

IF 'this is A test'<>'this is a test'
PRINT 'Different'
ELSE
PRINT 'The Same'

IF 'this is A test'<>'this is a test' COLLATE SQL_Latin1_General_CP1250_CS_AS
PRINT 'Different'
ELSE
PRINT 'The Same'

Results
----------

The Same
Different

Use COLLATE to cast one of the expressions to a case sensitive collation.Varbinary conversion is necessary in SQL 7 as it does not feature COLLATIONS however in SQL2000 I'd use COLLATE.

HTH
Jasper Smith

Edited by - jasper_smith on 07/29/2002 08:41:08

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources