Removing repeated commas with ArcGIS Survey123

Question

I have created a form in ArcGIS Survey123. I have several field where you can enter multiple numbers from a select_multiple question. I then want to put all of those numbers into a single string. So I have created a new field and used the calculate column to concatenate the outputs of all the other columns. I am not a coder and I'm not sure what coding language the ArcGIS Survey XML form actually uses so my searching on the internet has not come up with a solution.

Example of what I am trying to achieve:
field 1 contains 10,12,13;
field 2 contains null;
field 3 contains null;
field 4 contains null;
field 5 contains 56,67,90.

I want to end up with a new field containing 10,12,13,56,67,90 but concatenate, with a comma added between fields, gives me 10,12,13,,,56,67,90. Or concatenate with no comma added between fields gives me 10,12,1356,67,90. How do I remove the duplicated commas? I though a regex might do it but I can't work out how to build it.

Answer 1

This regex expression should find any extra commas the way your are wanting.

(?<=\w)\,+(?=$|\,)|(?<=^)\,+

Explanation:

This will select all repeating inside/trailing commas

1. (?<=\w): positive look-behind, match any word character; non-selecting
2. \,+: if 1 is satisfied, select as many comma literals as possible, stop where 3 is satistified
3. (?=$|\,): positive look-ahead, match end-of-string or a single comma literal, non-selecting

|: or

This will select all leading commas

1. (?<=^): positive look-ahead, match the start-of-string, non-selecting
2. \,+: if 1 is satisfied, then select all comma literals possible

Then using Python's re.sub() you can replace them with an empty string.

orig_str = ",,,0,12,13,,,a,,56,67,90,,,"
new_str = re.sub(r"(?<=\w)\,+(?=$|\,)", r"", orig_str)
new_str = "0,12,13,a,56,67,90"

BUT

You should examine why are you doing what you are doing. This is kind of workaround is what is often called a 'code smell' in the programming domain. It is trying to use a system in a capacity where there is a better solution; using a hammer to drive a screw.

You may be very skilled in what you are doing and I obviously don't have the insight you do for your project; so choose to ignore this if you want. But from the outside, this seems to be an overcomplication or not choosing your project parameters more carefully.

As user30184 pointed out, if you remove separators how do you know what value was selected in which field? If the final values of the string is treated all equally then why treat the questions differently? Why not have a user select all values in one question. Or do you need define your questions better?

Again, do take a minute to review your decision. Your data schema is arguably the most important part of any GIS project, should be the most examined and toiled. It is probably the most difficult to fix afterwards.

With a bit more information I don't think asking help for schema design in another question would be inappropriate here at GIS.SE or potentially CrossValidate.SE.