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

• she_weeds: 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...