SELECT only rows that contain only alphanumeric characters in MySQL

Question

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.

Answer 1

Try REGEXP '^[A-Za-z0-9]+$'

This makes sure that all characters match.

Answer 2

Try this:

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

As regexp is not case sensitive except for binary fields.

Answer 3

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