Comparing strings - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Comparing strings

Author

Topic

mkh786
Starting Member

3 Posts

Posted - 2005-11-29 : 10:14:42

In my sqlserver 2000 settings, my coallation is such that when two strings are compared that have the same verbiage with one being upper and the othe string being lower the compare does not work.
for example 'TEST' and 'test' return to be the same, however I need to get a value TRUE when comparing such strings.
Without changing my coalltion in the datase to avoid other issues, Is there any way that I can compare 2 strings, one is uppercase and the other lowercase with some t-sql function

Any help would be greatly appreciated

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-29 : 10:16:41

UPPER(lowerCaseCol) = upperCase Col

?????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mkh786
Starting Member

3 Posts

Posted - 2005-11-29 : 10:51:32

This does not work - if variable a has 'TEST' and b has 'test' - i want the result to be false when comparing these two

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-29 : 12:42:05

select binary_checksum('test')
select binary_checksum('TEST')

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-29 : 13:05:36

quote:
Originally posted by mkh786

however I need to get a value TRUE when comparing such strings.

Well which is it?

DECLARE @x varchar(5), @y varchar(5)
SELECT @x = 'test', @y = 'TEST'
SELECT CASE WHEN binary_checksum(@x) = binary_checksum(@y) THEN 'TRUE' ELSE 'FALSE' END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mkh786
Starting Member

3 Posts

Posted - 2005-11-30 : 13:12:02

Than You - this works jus fine - God Bless!

quote:
Originally posted by X002548

quote:
Originally posted by mkh786

however I need to get a value TRUE when comparing such strings.

Well which is it?

DECLARE @x varchar(5), @y varchar(5)
SELECT @x = 'test', @y = 'TEST'
SELECT CASE WHEN binary_checksum(@x) = binary_checksum(@y) THEN 'TRUE' ELSE 'FALSE' END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-01 : 03:47:14

Also consider this

DECLARE @x varchar(5), @y varchar(5)
SELECT @x = 'test', @y = 'TEST'
SELECT CASE WHEN convert(varbinary(5),@x) = convert(varbinary(5),@y) THEN 'TRUE' ELSE 'FALSE' END

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 04:05:36

You can use COLLATE to core case sensitive comparisons too:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Case+Sensitive+comparison

There is a miniscule mathematical possibility that binary_checksum will generate the same value for different strings

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources