Combining two types of conditions to replace strings with regular expressions in QGIS

Question

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

Answer 1

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