Below are two methods for returning rows that only contain alphanumeric characters in MariaDB.

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:]

MariaDB’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.

Note that 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 specify a range of characters within your regular expression.

Example:

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

Result:

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

You’ll notice that this returned less rows than with our first example. This is 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             |
+-------------------+