Oracle equivalent to MS-Access character range/set

Question

In MS-Access, I can use character ranges/sets in the where clause:

SELECT
 table_name
FROM 
 all_tab_columns
WHERE
 table_name NOT LIKE 'A[0-9]*' 
 AND table_name NOT LIKE 'D[0-9]*' 
 AND table_name NOT LIKE 'S[0-9]*'

The character range/set is the range of numbers between 0-9. All tables that start with A, D or S, followed by a number, are eliminated from the query.

How can I do this in Oracle?

Answer 1

Use regular expressions:

SELECT
 table_name
FROM 
 all_tab_columns
WHERE
 NOT REGEXP_LIKE(table_name,'^(A|D|S)[0-9]')

• Balazs Papp: Careful, this is not the same. Your requirement is to exclude tables starting with A or D or S, so this should be ^A[0-9] and so on (where ^ marks the start of the line). It can be combined into a single expression: ^(A|D|S)[0-9].