Oracle query to find string not containing characters

Question

What is an Oracle query that will return all records where the field mytable.myname contains any characters other than

1. A-Z
2. a-z
3. 0-9
4. -/\()

Answer 1

You can use the following:

SELECT * FROM mytable WHERE REGEXP_LIKE (myname, '^[^a-zA-Z0-9\/\\()-]+$');

You can also do the same with an i modifier:

SELECT * FROM mytable WHERE REGEXP_LIKE (myname, '^[^a-z0-9\/\\()-]+$', 'i');

Explanation:

• ^ start of the string
• [^___ ] negative character set (which will match any character other than the characters specified inside it)
• + match the previous group more than once
• $ end of the string