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

Non-alphanumeric characters include punctuation characters like !@#&()–[{}]:;',?/* and symbols like `~$^+=<>“, as well as whitespace characters like the space or tab 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 only alphanumeric characters, then negate that with the NOT operator.

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

Result:

+----------------------+
| c1                   |
+----------------------+
| ()                   |
| !@#&()–[{}]:;',?/*   |
| `~$^+=<>“            |
|                      |
+----------------------+

If you need to exclude a specific character, you can always use the NULLIF() function.

For example, let’s exclude the empty string from the last row:

SELECT c1 FROM t1
WHERE NULLIF(c1, '') NOT REGEXP '[[:alnum:]]';

Result:

+----------------------+
| c1                   |
+----------------------+
| ()                   |
| !@#&()–[{}]:;',?/*   |
| `~$^+=<>“            |
+----------------------+

Option 2: Specify a Range of Characters

Another way to do it is to specify a range of characters within your regular expression.

Example:

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

Result:

+----------------------+
| c1                   |
+----------------------+
| ()                   |
| !@#&()–[{}]:;',?/*   |
| `~$^+=<>“            |
|                      |
| é                    |
| É                    |
| ø                    |
+----------------------+

And to remove the empty string:

SELECT c1 FROM t1
WHERE NULLIF(c1, '') NOT REGEXP '[A-Za-z0-9]';

Result:

+----------------------+
| c1                   |
+----------------------+
| ()                   |
| !@#&()–[{}]:;',?/*   |
| `~$^+=<>“            |
| é                    |
| É                    |
| ø                    |
+----------------------+

In this case, my exclusion range didn’t cover alphanumeric characters like é, É, and ø, and so the output is not a true representation of non-alphanumeric characters. That said, at least this method gives you the option to specify the exact characters you want to include or exclude from the result.

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 NOT RLIKE or NOT REGEXP_LIKE().

Example of RLIKE:

SELECT c1 FROM t1 
WHERE c1 NOT RLIKE '[[:alnum:]]';

Example of REGEXP_LIKE():

SELECT c1 FROM t1 
WHERE NOT REGEXP_LIKE(c1, '[[:alnum:]]');