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
'%& #91;abcdefghijklmnop
qrstuvwxyz]%'

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
> '%& #91;abcdefghijklmnop
qrstuvwxyz]%'

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: 
>
>   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.
Oz