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
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.