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.

Solution 1:

Try this code:

SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9]+$'

This makes sure that all characters match.

Solution 2:

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

Solution 3:

Try this:

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

As regexp is not case sensitive except for binary fields.

Solution 4:

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.