Below are two methods for returning rows that only contain 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.
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:]]+$');Result:
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 REGEXP_LIKE(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 REGEXP_LIKE(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 your regular expression.
Example:
SELECT c1 FROM t1
WHERE REGEXP_LIKE(c1, '^[A-Za-z0-9]+$');Result:
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.
Here it is again with a range that includes those characters:
SELECT c1 FROM t1
WHERE REGEXP_LIKE(c1, '^[A-Za-zÀ-Þß-ÿ0-9]+$');Result:
Music Café 007 é É ø
We can include spaces like this:
SELECT c1 FROM t1
WHERE REGEXP_LIKE(c1, '^[A-Za-zÀ-Þß-ÿ0-9 ]+$');Result:
Music Live Music Café Café Del Mar 100 Cafés Player 456 007 é É é 123 ø ø 123
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 REGEXP_LIKE(c1, '[A-Za-zÀ-Þß-ÿ0-9]');Result:
Music Live Music Café Café Del Mar 100 Cafés [email protected] 1 + 1 $1.50 Player 456 007 é É é 123 ø ø 123
Or if we don’t want to include those unicode characters, we can simplify it to this:
SELECT c1 FROM t1
WHERE REGEXP_LIKE(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
This doesn’t exclude those unicode characters from the output if they happen to share a row with alphanumeric characters.