0

I'm looking for a regexp to get the correct output

For my example:

SELECT regexp_substr('brablcdefghig', '[^(bl)]+$') FROM dual;

I expect evth what is follow 'bl': cdefghig and it's OK,

But when I modify input and add 'b' charcter I've NULL in output why?

 SELECT regexp_substr('brablcdefghigb', '[^(bl)]+$') FROM dual;
tripleee
  • 175,061
  • 34
  • 275
  • 318
Kilro
  • 39
  • 1
  • 4
  • I didn't quite understand. Are you looking for the first occurrence of the substring `bl`, and you want to return everything that follows after that? What you wrote doesn't do that; it looks for the longest possible substring, at the end of your input string, consisting of characters that are not `b`, `l`, opening parenthesis or closing parenthesis. Almost surely **not** what you were trying to do. Please explain in plain English, not in code, **exactly** what you need; including exceptions (for example an input string that does not contain `bl` anywhere). –  May 19 '21 at 20:42

1 Answers1

0

That's a simple substr + instr; you don't need regular expressions. If it has to be regexp, see lines #8 and 9

SQL> with test (id, col) as
  2    (select 1, 'brablcdefghig'     from dual union all
  3     select 2, 'brablcdefghigb'    from dual
  4    )
  5  select id,
  6         col,
  7         substr(col, instr(col, 'bl') + 2) result,
  8         regexp_substr(replace(col, 'bl', '#'), '[^#]+$') result2,
  9         regexp_replace(col, '.+bl', '') result3
 10  from test;

        ID COL            RESULT     RESULT2    RESULT3
---------- -------------- ---------- ---------- ----------
         1 brablcdefghig  cdefghig   cdefghig   cdefghig
         2 brablcdefghigb cdefghigb  cdefghigb  cdefghigb

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57