Oracle regexp_like negating special char

Question

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.

You might find http://www.regexper.com/ useful for trying out the answers. It gives you a visual representation of the regex and has saved me hours — Dave, May 17 '13 at 16:18

femtoRgon · Answer 1 · 2013-05-17T16:00:46.363

1

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).

edited May 17 '13 at 16:00

answered May 17 '13 at 15:41

femtoRgon

32,893
7
60
87

1

This is incorrect. Test it on a string containing backslash. – Egor Skriptunoff May 17 '13 at 15:55
Right, Oracle regexes don't go for escaping in character classes. – femtoRgon May 17 '13 at 16:02

tbone · Answer 2 · 2013-05-17T16:19:17.363

I would probably use NOT regexp_like, which is more clear. But since you mentioned you don't want to use NOT, then I would probably use this (again, more clear imo):

select 'Does NOT contain dash or underscore' as val
from dual 
where regexp_instr('ABC123', '[-_]') = 0;

I'm sure you'd have about 20 different regexp versions soon ;-)

If you care about special treatment of empty strings(nulls), use a simple nvl:

nvl(regexp_instr('', '[-_]'),0) = 0;

I mention this because using regexp_like does not allow for this (nvl isn't a relational operator, but in this case we're comparing instr to a number, so we can use nvl on the instr (left hand) part.

But granted this depends on whether you want to say anything about the existence or non-existence of some characters in a null ;-)

score 0 · Accepted Answer · answered May 17 '13 at 15:54

0

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

answered May 17 '13 at 15:54

Egor Skriptunoff

23,359
2
34
64

Cool, that works. Why do the anchors make this work? and why is the * necessary? Thanks – mbow Jun 27 '13 at 18:26
@user2379830 - Look at femtoRgon's answer (about `*` and anchors). – Egor Skriptunoff Jun 27 '13 at 18:42

Oracle regexp_like negating special char

3 Answers3