Below are two methods for returning rows that only contain alphanumeric characters in PostgreSQL.
Alphanumeric characters are alphabetic characters and numeric characters.
Sample Data
We’ll use the following data for our examples:
SELECT c1 FROM t1;Result:
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 the value to a regular expression.
PostgreSQL’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 ~ '^[[:alnum:]]+$';Result:
Music Café 007 é É ø
That returns just the values that consist solely 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 ~ '^[[:alnum:] ]+$';Result:
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 ~ '[[:alnum:]]';Result:
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 the regular expression.
Example:
SELECT c1 FROM t1
WHERE c1 ~ '^[A-Za-z0-9]+$';Result:
Music 007
You’ll notice that 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, it pays to be especially mindful when using this method. It would be easy to accidentally exclude characters that you should include.
However, keeping with this range, we can include spaces like this:
SELECT c1 FROM t1
WHERE c1 ~ '^[A-Za-z0-9 ]+$';Result:
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 ~ '[A-Za-z0-9]';Result:
Music Live Music Café Café Del Mar 100 Cafés [email protected] 1 + 1 $1.50 Player 456 007 é 123 ø 123