Below are two options for finding those rows that contain alphanumeric characters in MySQL.

Alphanumeric characters are alphabetic characters and numeric characters.

Sample Data

We’ll use the following data for our examples:

CREATE TABLE t1 (
    c1 varchar(255) NULL
    );

INSERT INTO t1 VALUES
    ('Music'),
    ('Live Music'),
    ('Café'),
    ('Café Del Mar'),
    ('100 Cafés'),
    ('[email protected]'),
    ('1 + 1'),
    ('()'),
    ('!@#&()–[{}]:;'',?/*'),
    ('`~$^+=<>“'),
    ('$1.50'),
    ('Player 456'),
    ('007'),
    (null),
    (''),
    ('é'),
    ('É'),
    ('é 123'),
    ('ø'),
    ('ø 123');
SELECT c1 FROM t1;

Result:

+----------------------+
| c1                   |
+----------------------+
| Music                |
| Live Music           |
| Café                 |
| Café Del Mar         |
| 100 Cafés            |
| [email protected]    |
| 1 + 1                |
| ()                   |
| !@#&()–[{}]:;',?/*   |
| `~$^+=<>“            |
| $1.50                |
| Player 456           |
| 007                  |
| NULL                 |
|                      |
| é                    |
| É                    |
| é 123                |
| ø                    |
| ø 123                |
+----------------------+

Option 1: Compare to [:alnum:]

We can use MySQL’s REGEXP operator to compare our column to a regular expression.

MySQL’s regular expression capability includes support for the POSIX character classes. Therefore, we can use the [:alnum:] POSIX character class in our regular expressions to find the rows that contain alphanumeric characters.

SELECT c1 FROM t1 
WHERE c1 REGEXP '^[[:alnum:]]+$';

Result:

+-------+
| c1    |
+-------+
| Music |
| Café  |
| 007   |
| é     |
| É     |
| ø     |
+-------+

That returned only those rows that contain nothing but alphanumeric characters. If a row contains both alphanumeric and non-alphanumeric characters, it is not returned.

The space character is considered non-alphanumeric, and so if we want to include spaces, we can do this:

SELECT c1 FROM t1 
WHERE c1 REGEXP '^[[:alnum:] ]+$';

Result:

+---------------+
| c1            |
+---------------+
| Music         |
| Live Music    |
| Café          |
| Café Del Mar  |
| 100 Cafés     |
| Player 456    |
| 007           |
| é             |
| É             |
| é 123         |
| ø             |
| ø 123         |
+---------------+

To return all rows that contain alphanumeric characters (even if the row also contains non-alphanumeric characters), we can do this:

SELECT c1 FROM t1 
WHERE c1 REGEXP '[[:alnum:]]';

Result:

+-------------------+
| c1                |
+-------------------+
| Music             |
| Live Music        |
| Café              |
| Café Del Mar      |
| 100 Cafés         |
| [email protected] |
| 1 + 1             |
| $1.50             |
| Player 456        |
| 007               |
| é                 |
| É                 |
| é 123             |
| ø                 |
| ø 123             |
+-------------------+

Option 2: Specify a Range of Characters

Another way to do it is to provide a regular expression that provides a range of characters. We can still use the REGEXP operator to do this.

Example:

SELECT c1 FROM t1 
WHERE c1 REGEXP '^[A-Za-z0-9]+$';

Result:

+-------+
| c1    |
+-------+
| Music |
| 007   |
+-------+

This returned less rows than with our first example. That’s because I didn’t include the é, É, or ø characters in my range, and so any rows that contain those characters are excluded from the output.

Therefore, you’ll need to be careful when using this method, in case you accidentally exclude characters that you should include.

However, keeping with this range, we can include spaces like this:

SELECT c1 FROM t1 
WHERE c1 REGEXP '^[A-Za-z0-9 ]+$';

Result:

+------------+
| c1         |
+------------+
| Music      |
| Live Music |
| Player 456 |
| 007        |
+------------+

And we can use the following to include all rows that contain characters from our range (even if they also contain characters outside this range):

SELECT c1 FROM t1 
WHERE c1 REGEXP '[A-Za-z0-9]';

Result:

+-------------------+
| c1                |
+-------------------+
| Music             |
| Live Music        |
| Café              |
| Café Del Mar      |
| 100 Cafés         |
| [email protected] |
| 1 + 1             |
| $1.50             |
| Player 456        |
| 007               |
| é 123             |
| ø 123             |
+-------------------+

Alternatives to REGEXP

In MySQL, the RLIKE and REGXP operators are synonyms for REGEXP_LIKE(). Therefore, we can replace any of the previous examples with RLIKE or REGEXP_LIKE().

Example of RLIKE:

SELECT c1 FROM t1 
WHERE c1 RLIKE '^[[:alnum:]]+$';

Example of REGEXP_LIKE():

SELECT c1 FROM t1 
WHERE REGEXP_LIKE(c1, '^[[:alnum:]]+$');