Case Sensitive Pattern Match

[ozburger]

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?

[Erland Sommarskog]

(oz******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[ozburger]

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:

(oz******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

(oz******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[ozburger]

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