Case-sensitivity and LIKE - not working in 2000?

14 Sep 2006 6:43 PM

Paschott

Ultimate goal - find all addresses that are either all lowercase or all
uppercase. Running SQL 2000, hotfix level between SP3 and SP4.

Created a new table of address data that is case-sensitive.

CREATE TABLE Address
(Address_ID int
, House_ID int
, Address_1 varchar(50) NOT NULL COLLATE SQL_Latin1_General_CP850_CS_AS
, Address_2 varchar(50) NOT NULL COLLATE SQL_Latin1_General_CP850_CS_AS
, City varchar(50) NOT NULL COLLATE SQL_Latin1_General_CP850_CS_AS
, State varchar(50) NOT NULL COLLATE SQL_Latin1_General_CP850_CS_AS
, Postal_Code varchar(50) NOT NULL COLLATE
SQL_Latin1_General_CP850_CS_AS)

When I populate the table with various values, I can verify that the
case-sensitivity works because if I insert a state value of 'ca' and
another row with 'CA', then the following will only return one of those
rows:

select * from Address where State = 'ca'

However, I get both rows if I try to use:

select * from Address where State like '%[a-z]%'
or
select * from Address COLLATE SQL_Latin1_General_CP850_CS_AS) where
State like '%[a-z]%'

This seems wrong to me because I should only be getting the value that
contains a lower-case character somewhere in the mix.

I was finally able to get my query to work, but by using UPPER() or
LOWER() around the columns I wanted to compare.

Am I using the wrong collation or otherwise doing something incorrectly
with my LIKE query?

Thanks.

-Peter Schott

14 Sep 2006 6:51 PM

Anith Sen

COLLATE keyword should succeed the column name not the table name. So you
can have:

SELECT *
FROM Address
WHERE State COLLATE SQL_Latin1_General_CP850_CS_AS
LIKE '%[a-z]%'

--
Anith

14 Sep 2006 7:24 PM

Tom Cooper

This is because when you do a like [a-z], it means you want one character
between a and z inclusive in the collation sequence you are using. Since
you have a case sensitive collation, it's order is AaBbCcDd...YyZz.
(Actually there would also be a bunch of accented letters in there as well.)
Since you asked for [a-z], that turns out to be every lower case letter plus
every upper case letter except A. So it finds CA in %[a-z]% because of the
C, if you entered a state of AA, that would not be found by Like '%[a-z]%'.

Tom

Show quote

"Paschott" <pasch***@gmail.com> wrote in message
news:1158259416.221728.136970@b28g2000cwb.googlegroups.com...
> Ultimate goal - find all addresses that are either all lowercase or all
> uppercase. Running SQL 2000, hotfix level between SP3 and SP4.
>
>
> Created a new table of address data that is case-sensitive.
>
> CREATE TABLE Address
> (Address_ID int
> , House_ID int
> , Address_1 varchar(50) NOT NULL COLLATE SQL_Latin1_General_CP850_CS_AS
> , Address_2 varchar(50) NOT NULL COLLATE SQL_Latin1_General_CP850_CS_AS
> , City varchar(50) NOT NULL COLLATE SQL_Latin1_General_CP850_CS_AS
> , State varchar(50) NOT NULL COLLATE SQL_Latin1_General_CP850_CS_AS
> , Postal_Code varchar(50) NOT NULL COLLATE
> SQL_Latin1_General_CP850_CS_AS)
>
> When I populate the table with various values, I can verify that the
> case-sensitivity works because if I insert a state value of 'ca' and
> another row with 'CA', then the following will only return one of those
> rows:
>
> select * from Address where State = 'ca'
>
> However, I get both rows if I try to use:
>
> select * from Address where State like '%[a-z]%'
> or
> select * from Address COLLATE SQL_Latin1_General_CP850_CS_AS) where
> State like '%[a-z]%'
>
>
> This seems wrong to me because I should only be getting the value that
> contains a lower-case character somewhere in the mix.
>
> I was finally able to get my query to work, but by using UPPER() or
> LOWER() around the columns I wanted to compare.
>
>
> Am I using the wrong collation or otherwise doing something incorrectly
> with my LIKE query?
>
> Thanks.
>
> -Peter Schott
>

14 Sep 2006 10:45 PM

Erland Sommarskog

Paschott (pasch***@gmail.com) writes:

> select * from Address where State = 'ca'
>
> However, I get both rows if I try to use:
>
> select * from Address where State like '%[a-z]%'
> or
> select * from Address COLLATE SQL_Latin1_General_CP850_CS_AS) where
> State like '%[a-z]%'
>
> Am I using the wrong collation or otherwise doing something incorrectly
> with my LIKE query?

The first. Use a binary collation in your query. For explanation, see
Tom's post.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx