Oracle regexp_like negating special char

regex oracle oracle10g caret negate
mbow picture mbow · May 17, 2013 · Viewed 11.1k times · Source

I'm using Oracle 10g trying to EXCLUDE entries that contain a - or a _ with a caret in the character class in Oracle 10g. I can find the entries containing dash or underscore through:

WITH example
     AS (SELECT 'AAAA-1' n FROM DUAL
         UNION
         SELECT 'AAAAA_1' FROM DUAL
         UNION
         SELECT 'AAAA' FROM DUAL)
SELECT *
  FROM example
 WHERE REGEXP_LIKE (n, '[_\-]')

I know I can get by with using NOT but how do I negate this with a caret (^)? I've tried [^_\-] which returns everything, [^[_\-]] which returns nothing, and [^(_\-)] which is invalid.

Answer

femtoRgon picture femtoRgon · May 17, 2013

Try:

^[^_-]*$

I believe that [^_-] matches anything, because it is looking for Any character that is anything other than '_' or '-'. Similar to the opposite that works, [_-], which finds any character, anywhere in the string, that is either a '-' or '_'.

To change that, accept any number of character matching you character class [^_-], and surround with ^ (start of line) and $ (end of line).

Related questions

How to escape comma and double quote at same time for CSV file?

I am writing a Java app to export data from Oracle to csv file Unfortunately the content of data may quite tricky. Still comma is the deliminator, but some data on a row could be like this: | ID | FN | LN | …

Read More
Split string by space and character as delimiter in Oracle with regexp_substr

I'm trying to split a string with regexp_subtr, but i can't make it work. So, first, i have this query select regexp_substr('Helloworld - test!' ,'[[:space:]]-[[:space:]]') from dual which very nicely extracts my …

Read More
Oracle REGEXP_LIKE and word boundaries

I am having a problem with matching word boundaries with REGEXP_LIKE. The following query returns a single row, as expected. select 1 from dual where regexp_like('DOES TEST WORK HERE','TEST'); But I want to match on word boundaries …

Read More