compare varchar column values case-sensitively?

22 Dec 2005 9:36 PM

n_o_s_p_a__m

Is it possible to case-sensitively compare 2 varchar column values
without changing the collation on the column to case-sensitive? For
example, I have column called Note on a table called Operation, and I'd
like to select distinct note, case-sensitively, without changing the
collation.
Possible?

-KJ

22 Dec 2005 9:40 PM

Rick Sawtell

<n_o_s_p_a***@mail.com> wrote in message

news:1135287389.111343.321820@g47g2000cwa.googlegroups.com...
> Is it possible to case-sensitively compare 2 varchar column values
> without changing the collation on the column to case-sensitive? For
> example, I have column called Note on a table called Operation, and I'd
> like to select distinct note, case-sensitively, without changing the
> collation.
> Possible?
>
> -KJ
>

SELECT    columnlist
FROM       tablename
JOIN         tablename
WHERE    col1 COLLATE <collation name> = col2 COLLATE <collation name>

Rick Sawtell

22 Dec 2005 9:51 PM

Trey Walpole

you can specify a collation on the select

e.g. [using pubs.authors out of the box]

use pubs

update authors
set au_lname = 'ringer' -- only duplicated last name
where au_id='899-46-2035'

select distinct au_lname collate SQL_Latin1_General_CP1_CS_AS
from authors
-- 23 rows return

select distinct au_lname
from authors
-- 22 rows return

n_o_s_p_a***@mail.com wrote:
Show quote

> Is it possible to case-sensitively compare 2 varchar column values
> without changing the collation on the column to case-sensitive? For
> example, I have column called Note on a table called Operation, and I'd
> like to select distinct note, case-sensitively, without changing the
> collation.
> Possible?
>
> -KJ
>

22 Dec 2005 10:13 PM

n_o_s_p_a__m

Thanks, Rick and Trey, for the quick and accurate replies!

-KJ