Selecting Where First Character Is Not A-Z

[Mcod]

Hi there, another short question regarding a query:

 

I want to show some data in categories called A - Z and in another category which contains non A-Z items.

 

For the a - z queries this is easy:

 

$thequery = mysql_query("SELECT * FROM allitems where thename like 'a%' ORDER by thename ASC LIMIT $offset, $limit");

 

However, I would also like to have a category with items that do not start with a - z, for example items like !Great or items that start with a number like 1Great or even things like *Great

 

Sure, I could do it in a dirty way like:

 

where thename NOT like 'a%' AND thename NOT like 'b%' AND thename NOT like 'c%'

 

but I am sure there is a better solution with some REGEX query or something similar which I don't know about.

 

Maybe you have an idea how this would work out best?

 

Thank you

[requinix]

I'm quite sure you can go with string comparisons:

WHERE thename < 'A' OR (thename >= '[' AND thename < 'a') OR thename >= '{'

[ and { being the characters immediately after Z and z respectively.

 

What would be nice is if you had a column just for the first letter (always in upper- or lowercase), then it'd be really easy:

WHERE thename BETWEEN 'A' AND 'Z' /* letters */
WHERE thename NOT BETWEEN 'A' AND 'Z' /* not letters */

Edited September 23, 2012 by requinix

[PFMaBiSmAd]

WHERE your_column REGEXP '^[^a-z]'

[Barand]

If you're not up to speed with regex then

 

WHERE SUBSTR(thename,1,1) BETWEEN 'A' AND 'Z'

[Jessica]

If you're not up to speed with regex then

 

WHERE SUBSTR(thename,1,1) BETWEEN 'A' AND 'Z'

Isn't that the opposite of what OP wants?

[Barand]

It was meant to illustrate the existence of a function that he could use, but if you insist on being pedantic

 

WHERE SUBSTR(thename,1,1) NOT  BETWEEN 'A' AND 'Z'

[Jessica]

Sorry Barand, wasn't trying to be pedantic.

[Barand]

Forgiven

[The Little Guy]

I am fairly sure that regexp is much slower than "where col not like 'a%'" or even "where 'a' != left('a', 1)"