How to do a case-sensitive WHERE clause comparision?

Using SQL Server 2000
Database is setup to be case-insensitive
I want to do a query comparing password in a case-sensitive way. Is there an
easy way to do that?
SELECT COUNT(*)
FROM mytable
WHERE Username = 'someuser'
AND Password = 'somepassword'
Is there maybe a special character or a function that will match Password in
a case-sensitive way?
Thanks in advance.You can use either CONVERT or COLLATE:
AND CONVERT(VARBINARY, Password) = CONVERT(VARBINARY, 'somepassword')
or
AND Password COLLATE SQL_Latin1_General_CP1_CS_AS = 'somepassword'
"M" wrote:

> Using SQL Server 2000
> Database is setup to be case-insensitive
> I want to do a query comparing password in a case-sensitive way. Is there
an
> easy way to do that?
> SELECT COUNT(*)
> FROM mytable
> WHERE Username = 'someuser'
> AND Password = 'somepassword'
> Is there maybe a special character or a function that will match Password
in
> a case-sensitive way?
> Thanks in advance.
>
>|||try this
SELECT COUNT(*)
FROM mytable
WHERE binary_checksum(Username) = binary_checksum('someuser')
AND binary_checksum(Password) = binary_checksum('somepassword')
Message posted via http://www.webservertalk.com|||I don't recommend either of the suggestions already posted. Better is this:
select count(*) from mytable
where Username collate Latin1_General_CS_AS = 'someuser'
and Password collate Latin1_General_CS_AS = 'somepassword'
Use in the COLLATE clause the collation you have in place, but
with CI changed to CS.
Steve Kass
Drew University
M wrote:

>Using SQL Server 2000
>Database is setup to be case-insensitive
>I want to do a query comparing password in a case-sensitive way. Is there a
n
>easy way to do that?
>SELECT COUNT(*)
>FROM mytable
>WHERE Username = 'someuser'
>AND Password = 'somepassword'
>Is there maybe a special character or a function that will match Password i
n
>a case-sensitive way?
>Thanks in advance.
>
>|||Will this be better performance wise, or just more correct? I agree with
you that I would have suggested this, but not sure either of them were not
perfectly valid. Can you expand?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23ySDjivOFHA.4000@.TK2MSFTNGP15.phx.gbl...
>I don't recommend either of the suggestions already posted. Better is
>this:
> select count(*) from mytable
> where Username collate Latin1_General_CS_AS = 'someuser'
> and Password collate Latin1_General_CS_AS = 'somepassword'
> Use in the COLLATE clause the collation you have in place, but
> with CI changed to CS.
> Steve Kass
> Drew University
> M wrote:
>|||On Wed, 6 Apr 2005 22:43:59 -0400, Louis Davidson wrote:

>Will this be better performance wise, or just more correct? I agree with
>you that I would have suggested this, but not sure either of them were not
>perfectly valid. Can you expand?
Hi Louis,
I *think* that the CONVERT(VARBINARY ...) suggested by Jack is just as
correct (but slower). And I *know* that the binary_checksum(...)
suggested by baie dronk is less correct.
SELECT BINARY_CHECKSUM('a'), BINARY_CHECKSUM('aaaaaaaaaaaaaaaaa')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||That is what I thought (though at the time I had not seen the
binary_checksum issue.) Thanks.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3d8b515s66gptarutaqhkfvmfq3hgb55hm@.
4ax.com...
> On Wed, 6 Apr 2005 22:43:59 -0400, Louis Davidson wrote:
>
> Hi Louis,
> I *think* that the CONVERT(VARBINARY ...) suggested by Jack is just as
> correct (but slower). And I *know* that the binary_checksum(...)
> suggested by baie dronk is less correct.
> SELECT BINARY_CHECKSUM('a'), BINARY_CHECKSUM('aaaaaaaaaaaaaaaaa')
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Louis,
In some cases, casting to varbinary will yield different results than
applying
a case-sensitive collation.
if cast('password' as varbinary)
= cast('paword' as varbinary)
print 1
else
print 2
-- prints 2
if 'password' collate Latin1_General_CS_AS
= 'paword' collate Latin1_General_CS_AS
print 1
else
print 2
-- prints 1
Another difference is that trailing spaces do not affect case-
sensitive string comparisons, but they do affect binary comparisons.
And if an accent-insensitive collation is desired, you can't get it
if you convert to varbinary.
Also, while the OP was comparing with =, it's worth noting that the
ordering
of data won't be the same when considered as varbinary as when considered
as case-sensitive character data.
if cast('A' as varbinary)
< cast('a' as varbinary)
print 1
else
print 2
if 'A' collate Latin1_General_CS_AS
< 'a' collate Latin1_General_CS_AS
print 1
else
print 2
Louis Davidson wrote:

>Will this be better performance wise, or just more correct? I agree with
>you that I would have suggested this, but not sure either of them were not
>perfectly valid. Can you expand?
>
>|||Well, frankly this makes sens, other than why you would suggest using a
collation comparison in this case versus a binary one. I would actually
prefer 'password' and 'paword' to NOT compare as the same for a password,
while for almost any other reason I would like these to match.
The only change I didn't think to suggest would be to store the password at
the very least as a binary value, but probably in some form of at least mild
encryption.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23ZLVbzIPFHA.2468@.tk2msftngp13.phx.gbl...
> Louis,
> In some cases, casting to varbinary will yield different results than
> applying
> a case-sensitive collation.
> if cast('password' as varbinary)
> = cast('paword' as varbinary)
> print 1
> else
> print 2
> -- prints 2
> if 'password' collate Latin1_General_CS_AS
> = 'paword' collate Latin1_General_CS_AS
> print 1
> else
> print 2
> -- prints 1
> Another difference is that trailing spaces do not affect case-
> sensitive string comparisons, but they do affect binary comparisons.
> And if an accent-insensitive collation is desired, you can't get it
> if you convert to varbinary.
> Also, while the OP was comparing with =, it's worth noting that the
> ordering
> of data won't be the same when considered as varbinary as when considered
> as case-sensitive character data.
> if cast('A' as varbinary)
> < cast('a' as varbinary)
> print 1
> else
> print 2
> if 'A' collate Latin1_General_CS_AS
> < 'a' collate Latin1_General_CS_AS
> print 1
> else
> print 2
>
> Louis Davidson wrote:
>