String-Compare

[KG]

Hi,

When comparing this two varchar-variables I get TRUE even if both values are
different.

Example:
declare @a varchar(50)
declare @b varchar(50)
set @a = 'Anna'
set @b = 'ANNA'
if @a = @b print 'both strings are equal'

This comparison leads to the output: 'both strings are equal' which is
very painfull for my process.

Is there any database-setting or something like this to bring this kind of
comparison to work as I want?

thanks for any help.
KG

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----

[Anith Sen]

You can do it in a few ways:

DECLARE @a VARCHAR(50)
DECLARE @b VARCHAR(50)
SET @a = 'Anna'
SET @b = 'ANNA'

--#1 Using a case sensitive collation with the COLLATE keyword

IF @a = @b COLLATE Latin1_General_BIN
PRINT 'both strings are equal'
ELSE
PRINT 'both strings are NOT equal'

--#2 Using CAST to VARBINARY. Note that the default length is 30 & hence for
longer strings make sure you explicitly specify a length.

IF CAST(@a AS VARBINARY) = CAST(@b AS VARBINARY)
PRINT 'both strings are equal'
ELSE
PRINT 'both strings are NOT equal'

--#3 Use CHECKSUM or BINARY_CHECKSUM. There are some instances where this
cannot be reliable especially with longer strings

IF BINARY_CHECKSUM(@a) = BINARY_CHECKSUM(@b)
PRINT 'both strings are equal'
ELSE
PRINT 'both strings are NOT equal'

--
- Anith
( Please reply to newsgroups only )

[Erland Sommarskog]

KG (ngu...@greenmail.ch) writes:
> When comparing this two varchar-variables I get TRUE even if both values
> are different.
>
> Example:
> declare @a varchar(50)
> declare @b varchar(50)
> set @a = 'Anna'
> set @b = 'ANNA'
> if @a = @b print 'both strings are equal'
>
> This comparison leads to the output: 'both strings are equal' which is
> very painfull for my process.
>
> Is there any database-setting or something like this to bring this kind of
> comparison to work as I want?
>

To elaborate on Anith's reply, this is something which is indeed
configurable in SQL Server. Normally, you make the choice when you
install SQL Server whether you want a case-sensitive or a case-
insensitive collation. (As well if you want a German, French etc
sorting rules.)

You can change it after installation as well. If you are using SQL7 or
earlier, the setting is server wide, and changing the setting requires you
to rebuild all databases, including master.

In SQL2000, things are more flexible, and you can set the collation
per column if you like. You can also emply the COLLATE clause for a
specific expression. And if you want case-sensitive comparison only
in one place, and prefer case-insensitive the rest of the time, this
is the way to go.

However, if you entire application requires case-sensitive comparisons,
you should rebuild your database, so that it uses a case-sensitive
collation. And while you can mix collations on the same server, you
will find that life is easier, if everything uses the same collation,
so we're back on rebuilding master.

The reason you have to rebuild databases when you change collation, is
that the physical order of indexes depends on the collation.

--
Erland Sommarskog, SQL Server MVP, som...@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp