Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using REGEXP_LIKE to query with exclusion

715920Apr 27 2011 — edited Apr 30 2011
Dear all,
how to query a column that doesn't contain value with a particular letter in the middle of it using REGEXP_LIKE? Suppose I have the following:
SQL> select * from test;

TEMP
--------------------
john
joni
jane
johny
jonny

SQL> select temp from test where regexp_like(temp,'[^h]');

TEMP
--------------------
john
joni
jane
johny
jonny

SQL> select temp from test where regexp_like(temp,'[a-z]+[^h][a-z]+');

TEMP
--------------------
john
joni
jane
johny
jonny
The above code tries to query temp that doesn't contain letter 'h' in the middle (so it querries joni, jane, and jonny) but it cannot be done with
regexp_like(temp,'[^h]');
regexp_like(temp,'[a-z]+[^h][a-z]+');
since these 2 patterns satisfy all the available rows. What is the solution?

Best regards,
Val
This post has been answered by BobLilly on Apr 27 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 28 2011
Added on Apr 27 2011
12 comments
1,718 views