Alphanumeric characters are alphabetic characters and numeric characters.

Below are examples of using SQL to return rows that contain only alphanumeric characters.

Sample Data

We’ll use the following data for our examples:

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

Oracle Database

In Oracle Database, we can use the REGEXP_LIKE() function to compare the value to a regular expression.

Oracle’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 REGEXP_LIKE(c1, '^[[:alnum:]]+$');

Note that the space character is considered to be non-alphanumeric. To include spaces, we can add a space to the pattern:

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

SQL Server

With SQL Server we can use the LIKE operator with a pattern:

SELECT c1 FROM t1
WHERE c1 NOT LIKE '%[^a-zA-Z0-9]%'
AND c1 LIKE '%[a-zA-Z0-9]%';

And with spaces:

SELECT c1 FROM t1
WHERE c1 NOT LIKE '%[^a-zA-Z0-9 ]%'
AND c1 LIKE '%[a-zA-Z0-9 ]%';

MySQL

With MySQL, we can use the same method as with Oracle:

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

With spaces:

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

MariaDB

We can do the same thing with MariaDB:

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

With spaces:

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

PostgreSQL

PostgreSQL also supports the POSIX character classes, but instead of using the REGEXP operator like with some of the previous examples, we use the ~ operator instead:

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

And with spaces:

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

SQLite

In SQLite, we can use a range of characters:

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

However, to get all of the unicode characters from our sample table, we’ll need to include a range that includes those characters:

SELECT c1 FROM t1 
WHERE c1 REGEXP '^[A-Za-zÀ-Þß-ÿ0-9]+$';

And to include spaces, we can add a space to the pattern:

SELECT c1 FROM t1 
WHERE c1 REGEXP '^[A-Za-zÀ-Þß-ÿ0-9 ]+$';