[mysql] SELECT only rows that contain only alphanumeric characters in MySQL

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.

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.

Try this

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]+$';

^ and $ 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:]]+$';

Try this:

REGEXP '^[a-z0-9]+$'

As regexp is not case sensitive except for binary fields.

Change the REGEXP to Like

SELECT * FROM table_name WHERE column_name like '%[^a-zA-Z0-9]%'

this one works fine