MySql Not Like Regexp?

Question

I'm trying to find rows where the first character is not a digit. I have this:

SELECT DISTINCT(action) FROM actions 
WHERE qkey = 140 AND action NOT REGEXP '^[:digit:]$';

But, I'm not sure how to make sure it checks just the first character...

Answer 1

Your current regex will match values consisting of exactly one digit, not the first character only. Just remove the $ from the end of it, that means "end of value". It'll only check the first character unless you tell it to check more.

^[:digit:] will work, that means "start of the value, followed by one digit".

Answer 2

First there is a slight error in your query. It should be:

NOT REGEXP '^[[:digit:]]'

Note the double square parentheses. You could also rewrite it as the following to avoid also matching the empty string:

REGEXP '^[^[:digit:]]'

Also note that using REGEXP prevents an index from being used and will result in a table scan or index scan. If you want a more efficient query you should try to rewrite the query without using REGEXP if it is possible:

SELECT DISTINCT(action) FROM actions 
WHERE qkey = 140 AND action < '0'
UNION ALL
SELECT DISTINCT(action) FROM actions 
WHERE qkey = 140 AND action >= ':'

Then add an index on (qkey, action). It's not as pleasant to read, but it should give better performance. If you only have a small number of actions for each qkey then it probably won't give any noticable performance increase so you can stick with the simpler query.