MySQL - fetch rows where first character is not alphanumeric

Question

I am adding a few new features to a small project i'm working on and one of them is alpha pagination which looks like

# 0-9 A B C D E ... X Y Z

I can easily fetch items by their first letter using something like

SELECT * FROM ... WHERE name LIKE 'A%' ...

Grouping everything that starts with a number and all other characters is a little more difficult, I assume it would have to use MySQLs REGEXP.

Just to be clear, I need help creating two queries which will fetch all rows where

• the first character of a column is numeric
• the first character of a column is not alphanumeric

Answer 1

First character is numeric:

SELECT * FROM ... WHERE name REGEXP '^[0-9]';

First character is not alphanumeric:

SELECT * FROM ... WHERE name REGEXP '^[^0-9A-Za-z]';

(Note that this is distinct from NOT REGEXP ^[0-9A-Za-z], because you seem to only want to match when there is in fact a first character.)

You can probably substitute [^[:alnum:]] for [^0-9A-Za-z], but I haven't tested it. You can certainly substitute [[:digit:]] for [0-9], but it's longer. :-)

Also see the MySQL REGEXP Reference.