query SELECT only rows that contain only alphanumeric characters in MySQL
mysql regexp (5)
I'm trying to select all rows that contain only alphanumeric characters in MySQL using:
SELECT * FROM table WHERE column REGEXP '[A-Za-z0-9]';
However, it's returning all rows, regardless of the fact that they contain non-alphanumeric characters.
select count(*) from table where cast(col as double) is null;
Your statement matches any string that contains a letter or digit anywhere, even if it contains other non-alphanumeric characters. Try this:
SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9]+$';
$ require the entire string to match rather than just any portion of it, and
+ looks for 1 or more alphanumberic characters.
You could also use a named character class if you prefer:
SELECT * FROM table WHERE column REGEXP '^[[:alnum:]]+$';
As regexp is not case sensitive except for binary fields.
SELECT * FROM table_name WHERE column_name like '%[^a-zA-Z0-9]%'
this one works fine
There is also this:
select m from table where not regexp_like(m, '^[0-9]\d+$')
which selects the rows that contains characters from the column you want (which is m in the example but you can change).
Most of the combinations don't work properly in Oracle platforms but this does. Sharing for future reference.