| Author |
Case Sensitive Pattern Match
|
|
| ozburger@gmail.com 2005-08-31, 7:24 am |
| 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?
| |
| Erland Sommarskog 2005-08-31, 7:24 am |
| (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
| |
| ozburger@gmail.com 2005-08-31, 9:28 am |
| 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
| |
| Erland Sommarskog 2005-08-31, 8:23 pm |
| (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
| |
| ozburger@gmail.com 2005-08-31, 8:24 pm |
| 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
|
|
|
|