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
\(([^,]*?),([^,)]*?),?([^,)]*?)\), (\1\2\3) to support 2 or 3 elements, and can easily expand it to a reasonable maximum number of elements. Finding a general regex is tricky in this case...
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, @",(?<!\)[^\(]*)(?![^\)]*\()", ""));
,do you expect between the()?REGEXP_REPLACE. Mandatory comment: it doesn't look like data that should be raw in the database - specially if you're going to query it. Are you sure you cannot normalize it? (I know you probably can't at this point...)