I have a string with commas at the beginning, at the end and sometimes two or more commas after each other: ,,
, as can see at the bottom. I tried to remove the commas in a way to have a comma separated string, using regular expressions.
-
Deleting all commas at the beginning and the end of the string works fine:
regexp_replace (text, ('^,{1,}|,{1,}$'),'')
-
Replacing two or more commas with a single one inside the string works fine, too:
regexp_replace (text, (',{2,}'),',')
However, how to combine these two cases? Like:
- If there is a comma at the beginning or the end: delete it
- If there are two or more commas, replace it with one.
Is this possible in one single regex_replace string? What works is combining them – so the solution I'm looking for should accomplish what the follwoing expression with two regexp_replace
statements does, but using just a single regexp_replace
function:
regexp_replace (
regexp_replace (text, ('^,{1,}|,{1,}$'),''),
(',{2,}'),','
)
old_text new_text
,1 -> 1
1, -> 1
1,,2 -> 1,2
,,,1,2,3,4, -> 1,2,3,4
,1,2,3,4,5,,, -> 1,2,3,4,5
1,2,3,,,,,,6,7, -> 1,2,3,6,7
Best Answer
Would you consider skipping the dark arts of regex for an array expression?
This should do the trick.
array_to_string(array_filter(string_to_array(test,','),@element !=''),',')
For a purely regex solution try this:
regexp_replace(test,'^,*|,*$|(,)\\1+','\\1')