Reputation: 5279
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 .
Upvotes: 3
Views: 9360
Reputation: 4005
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
Upvotes: 0
Reputation: 82078
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
Upvotes: 4