Search code examples
regexoracle-databaseoracle10gcaretnegate

Oracle regexp_like negating special char

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.

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

    fiddle