CODEWITHSUNDEEP
phpmysqlsql
frosty
frosty

Reputation: 2649

Selecting all strings from database that starts with a lowercase letter

I'm trying to select all strings in my database that starts with a lowercase letter with regexp, but for some reason it's selecting all the strings that starts with a uppercase letter too. What am I doing wrong?

SELECT * 
FROM  `allData` 
WHERE response REGEXP  '^[a-z]'
LIMIT 0 , 30

Upvotes: 13

Views: 367

Answers (4)

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

select * from alldata where lower(left(response,1)) COLLATE Latin1_General_CS_AS =left(response,1) and response is not null and response<>''

Upvotes: 0

slugonamission
slugonamission

Reputation: 9642

From the MySQL REGEXP manual:

REGEXP is not case sensitive, except when used with binary strings.

You may therefore have some luck when using a binary string instead:

WHERE response REGEXP BINARY '^[a-z]'

Reasonably silly fiddle for demonstration: http://sqlfiddle.com/#!9/7eade/3

EDIT: As Ray says in the comments, you should probably use [[:lower:]] instead in order to work across all collations, e.g.

WHERE response REGEXP BINARY '^[[:lower:]]'

Upvotes: 11

Ray
Ray

Reputation: 41428

I would use mysql's Character Class Name to match in conjunction with REGEXP BINARY :

  WHERE response REGEXP BINARY  '^[[:lower:]]'

I don't know if [a-z] makes sense in every character set and collation, where as the character class name [:lower:] will always match all lower case alpha characters.

Upvotes: 2

Tony Chiboucas
Tony Chiboucas

Reputation: 5683

SELECT *
FROM allData
WHERE LOWER(LEFT(response, 1)) <> LEFT(response, 1)
LIMIT 0 , 30

...however, this may be limited by your MySQL character collation

Upvotes: 1

Related Questions