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

CASE

Is it possible to use a CASE statement without requiring equality? I don't
want to use a search case, but test a value for match or for range:
CASE @.Integer
WHEN 0 THEN do this
WHEN > 1 do this
WHEN <-1 do that
END
Thanks in advance.try
CASE
WHEN @.Integer = 0 THEN do this
WHEN @.Integer > 1 THEN do this
ELSE do that
END
"R Riness" wrote:

> Is it possible to use a CASE statement without requiring equality? I don't
> want to use a search case, but test a value for match or for range:
> CASE @.Integer
> WHEN 0 THEN do this
> WHEN > 1 do this
> WHEN <-1 do that
> END
> Thanks in advance.
>|||The alternatives for a CASE expression can't be
anything but expressions themselves. Since "do this"
doesn't sound like an expression, this might be more
what's needed (flow control).
Also watching out for the possibility that @.Integer can be
NULL, and avoiding an ELSE that happening more often
than it should:
if @.Integer = 0
begin [do this] end
else if @.Integer > 1
begin [do this] end
else if @.Integer < -1
begin [do this] end
Steve Kass
Drew University
Mike Gemmell wrote:
>try
>CASE
> WHEN @.Integer = 0 THEN do this
> WHEN @.Integer > 1 THEN do this
> ELSE do that
>END
>"R Riness" wrote:
>
>|||Now that I RTFD I know that a searched CASE is not what you want. I am
curious as what you have against a searched case.
If you are attempting something as simple as the example then you could try.
CASE SIGN(@.Integer)
WHEN 0 THEN
WHEN 1 THEN
WHEN -1 THEN
END
to test against a value you could
CASE SIGN(@.Integer - @.Intvalue)
WHEN 0 THEN match_result_expr
WHEN 1 THEN greater_than_result_expr
WHEN -1 THEN less_than_result_expr
END
"Mike Gemmell" wrote:
> try
> CASE
> WHEN @.Integer = 0 THEN do this
> WHEN @.Integer > 1 THEN do this
> ELSE do that
> END
> "R Riness" wrote:
>