I find an example at oracle forum site :
Input string : a, b, c (x, y, z), a, (xx, yy, zz), x,
WITH t AS (SELECT 'a, b, c (x, y, z), a, (xx, yy, zz), x,' col1
FROM dual)
SELECT t.col1
, REGEXP_REPLACE(t.col1, '(\(.*?\))|,', '\1') new_col
FROM t
Output : a b c (x, y, z) a (xx, yy, zz) x
But i want to make opposite of that. Just remove this character ,
from inside ()
and remain outside.
Output : a, b, c (x y z), a, (xx yy zz), x,
This will work for a constant length of arguments with in the brackets.
REGEXP_REPLACE(t.col1, '(\(.*?),(.*?),(.*?\))', '\1\2\3') new_col
update inspired by @Kobi's comment:
this regular expression removes the 1st, optional 2nd and optional 3rd ,
between ()
it can be extended up to 9 (I've a book stating \1 ... \500 should be possible but only \1 ... \9 worked)
REGEXP_REPLACE(t.col1, '\(([^,]*),([^,]*),?([^,]*),?([^,]*)\)', '(\1\2\3\4)') new_col
A little modified version of the regular expression you used:
REGEXP_REPLACE(column_name, '((\)|^).*?(\(|$))|,', '\1')
Not sure if REGEXP_REPLACE supports negative look aheads and look behinds, but if it does this would work: ,(?<!\)[^\(]*)(?![^\)]*\()
I tested with C#:
string s = "a, b, c (x, y, z), a, (xx, yy, zz), x,";
Console.WriteLine(Regex.Replace(s, @",(?<!\)[^\(]*)(?![^\)]*\()", ""));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With