REGEXP not working in mysql

Question

I am trying to find record with names which have non-alpha numeric characters.

I thought that I could do it with REGEXP

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Then I referred another SO question

How can I find non-ASCII characters in MySQL?

I found I could use this query :

SELECT * FROM tableName WHERE NOT columnToCheck REGEXP '[A-Za-z0-9]';

But it returns me zero rows . If I replaced the command to :

SELECT * FROM tableName WHERE columnToCheck REGEXP '[A-Za-z0-9]';

It returns me all the rows!!.

I tried some basic commands :

SELECT 'justffalnums' REGEXP '[[:alnum:]]'; returns 1

which is correct but

SELECT 'justff?alnums ' REGEXP '[[:alnum:]]'; also returns 1

I don't understand why it returs one. It should return 0 as it has space and also a '?' .

Is there anything to be enable in mysql for the regexp to work ?

I am using mysql 5.0 and tried with 5.1 too .

Answer 1

You need to add ^ (string begins) and $ (string ends) as well as an operator saying a certain number of alphanum's to use. Below I used + which means one or more.

SELECT 'justff?alnums ' REGEXP '^[[:alnum:]]+$';
-- only contains alphanumns => 0

SELECT 'justff?alnums ' REGEXP '^[[:alnum:]]+';
-- just begins with alphanum => 1

SELECT 'justff?alnums ' REGEXP '[[:alnum:]]+$';
-- just ends with alphanum => 0

Answer 2

The regex that you've given does not say that the entire field has to contain the characters in question. You can use the negation character ^ at the beginning of a character set.

SELECT 'justff?alnums' REGEXP '[^A-Za-z0-9]'; returns 1
SELECT 'justffalnums' REGEXP '[^A-Za-z0-9]'; returns 0