CODEWITHSUNDEEP
sqlregexoracle-databasecharacter
hamdi
hamdi

Reputation: 81

REGEXP_REPLACE - remove commas from string ONLY if enclosed in ()'s

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,

Upvotes: 8

Views: 6828

Answers (3)

Karolis
Karolis

Reputation: 9572

A little modified version of the regular expression you used:

REGEXP_REPLACE(column_name, '((\)|^).*?(\(|$))|,', '\1')

Upvotes: 2

bw_üezi
bw_üezi

Reputation: 4564

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

Upvotes: 3

Petar Ivanov
Petar Ivanov

Reputation: 93060

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, @",(?<!\)[^\(]*)(?![^\)]*\()", ""));

Upvotes: 1

Related Questions