Oracle regular expression to exclude values with a pattern

Question

I need a regular expression to exclude values of a particular pattern.

REGEXP_LIKE( name,'[0-9].rm')  # this returns values like 123rm, 456rm etc.

I need to exclude values with the above format.

I tried both of the following, but it's not returning anything.

REGEXP_LIKE( name,'^[[0-9].rm]') 
REGEXP_LIKE( name,'^([0-9].rm)')

Please help! Thanks in advance

what values do you want to exclude? do you mean numbers? can you show the expected result by example? — void
– void, Commented Jan 22, 2015 at 22:56
i want exactly the negation of the above reg expression. it should return values like abc12, abc, ab12ac, rm123, rm12abc etc — Arun Vijay
– Arun Vijay, Commented Jan 22, 2015 at 23:15

René Nyffenegger · Accepted Answer · 2015-01-23 06:45:55Z

1

Since you want the negation of your regexp, you should use NOT in the query condition:

select ...
  from ...
 where ...
   NOT REGEXP_LIKE( name,'[0-9].rm')

answered Jan 23, 2015 at 6:45

René Nyffenegger

40.9k36 gold badges180 silver badges321 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andie2302 · Accepted Answer · 2015-01-23 00:01:48Z

0

Have you tried:

SELECT * FROM table WHERE NOT REGEXP_LIKE(name, '[0-9].rm', 'c');

answered Jan 23, 2015 at 0:01

Andie2302

4,9254 gold badges26 silver badges46 bronze badges