7

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

1 Answer 1

7

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')

1
  • 3
    Sorry, I misread your question. Edited with regex answer, hopefully that works. Now if you'll excuse me I hear Tony the Pony calling me...
    – she_weeds
    Commented Dec 19, 2021 at 22:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.