1

Oracle 12cR1 - I have a web based application I am writing. I have the need to process a string and remove open/close parentheses and all text within. For example, if my input is 

This is a (bad) sample, (my) friend

the output should be

This is a sample, friend

I have this working in Javascript. I need it to work in BOTH javascript and Oracle SQL. The code in Javascript is

var s2 = s1.replace(/\([^()]*\)/g, '');

When I try to convert this to Oracle SQL, nothing happens, aka my output is exactly the same as my input...

select regexp_replace('This is a (bad) sample','/\([^()]*\)/g', '') from dual;

In searching, someone posted a different regexp expression, which I tried. Again, no change in the output.

select regexp_replace('This is a (bad) sample','(?<=\()[\d.]+(?=\))', '') from dual;

IDEALLY, what I would like is a regular expression that also handles nested parentheses, deleting the entire phrase.

This is a ((really) bad) example

should return

This is a example

For nested parentheses, the JavaScript expression matches on the inner most set of parentheses, so I just have to run my code twice, which works.

So in summary, what is an Oracle SQL statement that takes a string, and deletes (ideally nested) parentheses and all text within the parentheses? Note that there may be multiple sets of parentheses (not necessarily nested) so I need it to remove each occurrence of parentheses.

user1009073
  • 3,160
  • 7
  • 40
  • 82
  • Possible duplicate of [How can I match nested brackets using regex?](https://stackoverflow.com/questions/14952113/how-can-i-match-nested-brackets-using-regex) – Narendrasingh Sisodia Nov 02 '17 at 11:33

1 Answers1

6

You may use

select regexp_replace('This is a (bad) sample, (my) friend','\([^()]*\)', '') from dual

See the online Oracle demo

The regex delimiters that are used in JavaScript regex literals should not be part of the Oracle regex pattern (that is POSIX based).

enter image description here

If you also want to get rid of optional whitespace before the word, add \s* before that pattern:

regexp_replace('This is a (bad) sample, (my) friend','\s*\([^()]*\)', '')
                                                      ^^^

Where \s* matches 0+ whitespace chars.

Note you cannot deal with nested parentheses with Oracle regex as it does not support recursion/balanced constructs.

However, you may handle 2 levels of depth with

select regexp_replace(
  'This is a (bad) sample, (my) friend and this is a ((really) bad (and more bad)) example',
  '\s*\([^()]*(\([^()]*\)[^()]*)*\)',
   '') as Result from dual

See another Oracle demo and a regex demo.

Details

  • \s* - 0+ whitespaces
  • \( - a (
  • [^()]* - 0+ chars other than ( and )
  • (\([^()]*\)[^()]*)* - 0+ sequences of
    • \([^()]*\) - (, 0+ chars other than ( and ) and then )
    • [^()]* - 0+ chars other than ( and )
  • \) - a ).

enter image description here

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • That did it! Thanks – user1009073 Nov 02 '17 at 11:40
  • To handle the double (not nested) parentheses case, you can use this "select regexp_replace('This is a ((very)(really) bad) sample, (my) friend','\s*\(((\([^()]*\))+|[^()])*\)', '') from dual" – Yomna Fahmy Nov 02 '17 at 11:47
  • @YomnaFahmy I would unroll that pattern, [`\([^()]*(\([^()]*\)[^()]*)*\)`](https://regex101.com/r/SIsKKn/2). That way, it will be more efficient. However, it will only handle 1- or 2-level nested parentheses, so it is just a workaround for some edge cases. – Wiktor Stribiżew Nov 02 '17 at 11:49
  • @WiktorStribiżew Your pattern doesn't handle the single parentheses case. You can test it here https://www.regexpal.com/?fam=99428 – Yomna Fahmy Nov 02 '17 at 11:53
  • @YomnaFahmy No need to, I fixed the typo (forgot to put `^` in the first bracket expression), and added to the answer. – Wiktor Stribiżew Nov 02 '17 at 11:55