Below are two options for returning rows that only contain non-alphanumeric characters in PostgreSQL.

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:

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 PostgreSQL’s !~ operator to compare our column to a regular expression. We use this operator when we want to specify that the value does not match the 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 expression.

SELECT c1 FROM t1 
WHERE c1 !~ '[[:alnum:]]';

Result:

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

The empty string is also num-numeric, and so that example returned the row that contains the empty string.

We can exclude empty strings by using NULLIF():

SELECT c1 FROM t1 
WHERE NULLIF(c1, '') !~ '[[:alnum:]]';

Result:

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

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 !~ '[A-Za-z0-9]';

Result:

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

And to remove the empty string:

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

Result:

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

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.