Case Sensitive Pattern Match

I am trying to do a select statement with SQL 2000 for all records
containing any number of lowercase letters. I have tried the following
2 statements and they both seem to match both lowercase and uppercase
letters.

SELECT * FROM customers WHERE name LIKE '%[a-z]%'

SELECT * FROM customers WHERE name LIKE
'%[abcdefghijklmnopqrstuvwxyz]%'

Can anybody help?(ozburger@.gmail.com) writes:
> I am trying to do a select statement with SQL 2000 for all records
> containing any number of lowercase letters. I have tried the following
> 2 statements and they both seem to match both lowercase and uppercase
> letters.
> SELECT * FROM customers WHERE name LIKE '%[a-z]%'
> SELECT * FROM customers WHERE name LIKE
> '%[abcdefghijklmnopqrstuvwxyz]%'

SELECT * FROM customers
WHERE name COLLATE Latin1_General_BIN LIKE '%[a-z]%'

You must cast to a binary collation, as in a case-sensitive collation,
a-z ranges something like aBcDC ... Zz. And in a case-insenstive collation
a-z is equivalent to A-Z.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I tried what you said, but it is still matching both lowercase and
uppercase.
e.g. it matches all 3 records below.
CITY SOFTWARE
City Software
city software
I only want to return those records that contain lowercase letters (in
this case the 2nd two records only). Any other suggestions?

Thanks Oz

Erland Sommarskog wrote:
> (ozburger@.gmail.com) writes:
> > I am trying to do a select statement with SQL 2000 for all records
> > containing any number of lowercase letters. I have tried the following
> > 2 statements and they both seem to match both lowercase and uppercase
> > letters.
> > SELECT * FROM customers WHERE name LIKE '%[a-z]%'
> > SELECT * FROM customers WHERE name LIKE
> > '%[abcdefghijklmnopqrstuvwxyz]%'
> SELECT * FROM customers
> WHERE name COLLATE Latin1_General_BIN LIKE '%[a-z]%'
> You must cast to a binary collation, as in a case-sensitive collation,
> a-z ranges something like aBcDC ... Zz. And in a case-insenstive collation
> a-z is equivalent to A-Z.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(ozburger@.gmail.com) writes:
> I tried what you said, but it is still matching both lowercase and
> uppercase.
> e.g. it matches all 3 records below.
> CITY SOFTWARE
> City Software
> city software
> I only want to return those records that contain lowercase letters (in
> this case the 2nd two records only). Any other suggestions?

I ran this:

CREATE TABLE x (myname varchar(20) NOT NULL)
go
INSERT x(myname) VALUES ('CITY SOFTWARE')
INSERT x(myname) VALUES ('City software')
INSERT x(myname) VALUES ('city software')
go
SELECT * FROM x WHERE myname COLLATE Latin1_General_BIN LIKE '%[a-z]%'

This was the result:

myname
-------
City software
city software

(2 row(s) affected)

If you got any other result there may be other problems with your query.
Can you post it?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You are correct. I tried again this morning and it seems to work fine
now. Maybe I had a typo or something.
Anyway thanks for all of your help on this. Much appreciated.
Ozsql