Negative match set of strings in regexp_substr

Question

I want to match all characters in a string until I hit one of the following substrings:

'='
'+%'
'-%'

I had no problem negating a set of single characters (i.e. [^=+-]), and I can positively match the strings with

(=|\+%|-%)

but I can't seem to figure out the syntax for negating the set. Any suggestions?

score 0 · Answer 1 · answered May 03 '16 at 16:29

Couldn't do it with REGEXP_SUBSTR alone but here is one option:

with temp as
(
  select 'TEST1=TEST1' test1 from dual union all
  select 'TEST2+%TEST2' test1 from dual union all
  select 'TEST3+TEST3' test1 from dual union all
  select 'TEST4-%TEST4' test1 from dual union all
  select 'TEST5-TEST5' test1 from dual
)

  SELECT test1,
         CASE
           WHEN REGEXP_INSTR(test1, '+%|=|-%') = 0 THEN test1
           ELSE REGEXP_REPLACE(SUBSTR(test1, 1, REGEXP_INSTR(test1, '-%|+%|=')), '[-=+%]')
           END
         result

  FROM temp

Negative match set of strings in regexp_substr

1 Answers1