MS SQL Server - Case sensitive compare

Author

Case sensitive compare

Frank Rizzo

2005-09-28, 8:23 pm

Our database server is set up for case insensitive compares. In other
words, both
select 1 from tbl where OrderID = 'QQQ'
select 1 from tbl where OrderID = 'qqq'

will return a row. Is there a way to force a case sensitive comparison
in just this one query without messing with the server settings?

Thanks

Jerry Spivey

2005-09-28, 8:23 pm

Frank,

SQL 2000? Check out the COLLATE clause in the SQL Server BOL for your
expression.

HTH

Jerry

"Frank Rizzo" <none@none.com> wrote in message
news:uOtBpOHxFHA.2000@TK2MSFTNGP10.phx.gbl...
> Our database server is set up for case insensitive compares. In other
> words, both
> select 1 from tbl where OrderID = 'QQQ'
> select 1 from tbl where OrderID = 'qqq'
>
> will return a row. Is there a way to force a case sensitive comparison in
> just this one query without messing with the server settings?
>
> Thanks

Uri Dimant

2005-09-29, 3:23 am

Frank
select 1 from tbl where OrderID = 'QQQ' COLLATE Latin1_General_BIN

"Frank Rizzo" <none@none.com> wrote in message
news:uOtBpOHxFHA.2000@TK2MSFTNGP10.phx.gbl...
> Our database server is set up for case insensitive compares. In other
> words, both
> select 1 from tbl where OrderID = 'QQQ'
> select 1 from tbl where OrderID = 'qqq'
>
> will return a row. Is there a way to force a case sensitive comparison in
> just this one query without messing with the server settings?
>
> Thanks

ben brugman

2005-09-29, 7:23 am

This might be a solution :

SELECT *
FROM
aa A
INNER JOIN
aa B
ON
A.aa = B.aa
AND
CAST(A.aa AS varbinary) = CAST(B.aa AS varbinary)

The first compare in the ON clause is to make an efficient match of the two
fields to match. The second compare in the ON clause is to make sure that
the compare becomes case sensitive.
(The second on it's own is enough in a lot of queries, but the first might
speed up the process because the first compare can use indexes, the second
can not).

The following gives the same result. (This was given in one of the other
mails). (Maybe in this case the first compare can be ommitted, because
it might be possible that the second compare can use indexes.)
This method may be prefereble to the method suggested above.

SELECT *
FROM
aa A
INNER JOIN
aa B
ON
A.aa = B.aa
AND
( A.aa = B.aa COLLATE Latin1_General_BIN)

good luck,
ben brugman

"Frank Rizzo" <none@none.com> wrote in message
news:uOtBpOHxFHA.2000@TK2MSFTNGP10.phx.gbl...
> Our database server is set up for case insensitive compares. In other
> words, both
> select 1 from tbl where OrderID = 'QQQ'
> select 1 from tbl where OrderID = 'qqq'
>
> will return a row. Is there a way to force a case sensitive comparison
> in just this one query without messing with the server settings?
>
> Thanks

ben brugman

2005-09-29, 7:23 am

Additional,
In both examples, the first compare can speed
up the query. The Cast and Collate constructions
both do not use available indexes. Ben

"ben brugman" <ben@niethier.nl> wrote in message
news:eptJkZOxFHA.3300@TK2MSFTNGP09.phx.gbl...
> This might be a solution :
>
> SELECT *
> FROM
> aa A
> INNER JOIN
> aa B
> ON
> A.aa = B.aa
> AND
> CAST(A.aa AS varbinary) = CAST(B.aa AS varbinary)
>
> The first compare in the ON clause is to make an efficient match of the
two
> fields to match. The second compare in the ON clause is to make sure that
> the compare becomes case sensitive.
> (The second on it's own is enough in a lot of queries, but the first might
> speed up the process because the first compare can use indexes, the second
> can not).
>
> The following gives the same result. (This was given in one of the other
> mails). (Maybe in this case the first compare can be ommitted, because
> it might be possible that the second compare can use indexes.)
> This method may be prefereble to the method suggested above.
>
> SELECT *
> FROM
> aa A
> INNER JOIN
> aa B
> ON
> A.aa = B.aa
> AND
> ( A.aa = B.aa COLLATE Latin1_General_BIN)
>
>
> good luck,
> ben brugman
>
>
>
> "Frank Rizzo" <none@none.com> wrote in message
> news:uOtBpOHxFHA.2000@TK2MSFTNGP10.phx.gbl...
>
>

Sponsored Links