Sorting records in lower case

andy982183 · #1 (**permalink**) 03-31-10, 06:01

Hello,

I am working on a query, It finds records which are in lower case from table.

eg.

richard
john
andy

The below query which I have designed, shows the required output...
but fields containing numeric & null values are also displayed, which i don't require.

--
SELECT * FROM `table`
WHERE (NAME COLLATE latin1_bin ) = lower(NAME);
--

Kindly Advice
Thanks

healdem · #2 (**permalink**) 03-31-10, 06:13

so exclude those rows by using an appropriate WHERE clause
eg AND NOT IS NULL(name)
and/or modify MIKE-BIKE-KITE's regexp to exclude numbers, although to be fair regexp is not for the faint hearted

andy982183 · #3 (**permalink**) 03-31-10, 06:48

Quote:

Originally Posted by healdem

so exclude those rows by using an appropriate WHERE clause
eg AND NOT IS NULL(name)
and/or modify MIKE-BIKE-KITE's regexp to exclude numbers, although to be fair regexp is not for the faint hearted

Hey Healdem,

Designed this query for lower case...and got fully working !!

SELECT * FROM `table`
WHERE NAME regexp BINARY'^[a-z]'

Thanks

r937 · #4 (**permalink**) 03-31-10, 13:51

Quote:

Originally Posted by andy982183

Designed this query for lower case...and got fully working !!

Thanks

your testing was not very rigourous, was it

try this --

Code:

CREATE TABLE test_mixedcase
( name VARCHAR(99)
);

INSERT INTO test_mixedcase (name) VALUES 
 ( 'this is all lower case' )
,( 'this starts in lower case AND THEN GOES UPPER' )
;
SELECT * FROM test_mixedcase
WHERE name REGEXP BINARY '^[a-z]'

tell me which rows it returned

hint: bofadem

Pat Phelan · #5 (**permalink**) 03-31-10, 14:06

Code:

SELECT *
   FROM `table`
   WHERE name NOT LIKE '&#37;[^a-z]%'

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech