CODEWITHSUNDEEP
regexoracle-databaseascii
Marcus Leon
Marcus Leon

Reputation: 56669

Oracle query to find string not containing characters

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. -/\()

Upvotes: 4

Views: 19106

Answers (1)

karthik manchala
karthik manchala

Reputation: 13640

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

Upvotes: 9

Related Questions