Below are two methods for returning rows that only contain non-alphanumeric characters in Oracle Database.

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 (c1) 
    WITH c AS (
        SELECT 'Music' FROM DUAL UNION ALL
        SELECT 'Live Music' FROM DUAL UNION ALL
        SELECT 'Café' FROM DUAL UNION ALL
        SELECT 'Café Del Mar' FROM DUAL UNION ALL
        SELECT '100 Cafés' FROM DUAL UNION ALL
        SELECT '[email protected]' FROM DUAL UNION ALL
        SELECT '1 + 1' FROM DUAL UNION ALL
        SELECT '()' FROM DUAL UNION ALL
        SELECT '!@#&()–[{}]:;'',?/*' FROM DUAL UNION ALL
        SELECT '`~$^+=<>“' FROM DUAL UNION ALL
        SELECT '$1.50' FROM DUAL UNION ALL
        SELECT 'Player 456' FROM DUAL UNION ALL
        SELECT '007' FROM DUAL UNION ALL
        SELECT 'é' FROM DUAL UNION ALL
        SELECT 'É' FROM DUAL UNION ALL
        SELECT 'é 123' FROM DUAL UNION ALL
        SELECT 'ø' FROM DUAL UNION ALL
        SELECT 'ø 123' FROM DUAL)
SELECT * FROM c;

SELECT c1 FROM t1;

Result:

Music
Live Music
Café
Café Del Mar
100 Cafés
[email protected]
1 + 1
()
!@#&()–[{}]:;',?/*
`~$^+=<>“
$1.50
Player 456
007
é
É
é 123
ø
ø 123

Option 1: Compare to [:alnum:]

We can use Oracle’s REGEXP_LIKE() function to compare the value to a regular expression, then negate it with the NOT operator.

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 non-alphanumeric characters.

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

Result:

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

That returned only those rows that contain non-alphanumeric characters only. If a row contains both alphanumeric and non-alphanumeric characters, it is not returned.

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

Here’s an example of expanding the range to exclude those characters from the output:

SELECT c1 FROM t1 
WHERE NOT REGEXP_LIKE(c1, '[A-Za-zÀ-Þß-ÿ0-9]');

Result:

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