SELECT only rows that contain only alphanumeric characters in MySQL

MysqlRegex

Mysql Problem Overview

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.

Mysql Solutions

Solution 1 - Mysql

Try this code:

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

This makes sure that all characters match.

Solution 2 - Mysql

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 - Mysql

Try this:

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

As regexp is not case sensitive except for binary fields.

Solution 4 - Mysql

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.

Solution 5 - Mysql

Try this

select count(*) from table where cast(col as double) is null;

Solution 6 - Mysql

Change the REGEXP to Like

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

this one works fine

Categories
Recommended Mysql Solutions
Recommended Regex Solutions

Previous Solution:

XSLT - remove whitespace from template

XmlXslt

Next Solution:

How can I print out the value of a git configuration setting (core.autocrlf) on Windows?

GitNewline

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questioncode_burgarView Question on Stackoverflow
Solution 1 - MysqlAaron DigullaView Answer on Stackoverflow
Solution 2 - MysqlJohn KugelmanView Answer on Stackoverflow
Solution 3 - MysqlYannick MottonView Answer on Stackoverflow
Solution 4 - MysqlapolloView Answer on Stackoverflow
Solution 5 - MysqlAkshay SinghView Answer on Stackoverflow
Solution 6 - MysqlINTERESTING FACTSView Answer on Stackoverflow