Is there a way to remove extra separator(s) when using the strcat command?

[scottrunyon]

I have a search that is using the strcat command to string together text fields. My data looks something like this

Name Marbles Hopscotch Jacks Baseball
Mary Marbles Jacks Baseball
John Hopscotch Baseball
Fred Marbles Jacks

Wilma Hopscotch Jacks

My search contains
| eval Marbles = if(isnull(Marbles),"", "".Marbles )
| eval Hopscotch = if(isnull(Hopscotch),"", "|".Hopscotch )
| eval Jacks = if(isnull(Jacks),"", "|".Jacks )
| eval Baseball = if(isnull(Baseball),"", "|".Baseball )
|strcat Marbles Hopscotch Jacks Baseball Games

And the output look like

Name Games
Mary Marbles||Jacks|Baseball
John |Hopscotch||Baseball
Fred Marbles||Jacks|
Wilma |Hopscotch|Jacks|

Is there a way to remove the separators that are after a blank entry so my output look like -

Name Games
Mary Marbles|Jacks|Baseball
John Hopscotch|Baseball
Fred Marbles|Jacks
Wilma Hopscotch|Jacks

Any help is greatly appreciated

Scott

[woodcock · Accepted solution]

Like this:

|makeresults
| eval raw="Mary Marbles Jacks Baseball:John Hopscotch Baseball:Fred Marbles Jacks:Wilma Hopscotch Jacks"
| makemv raw delim=":"
| mvexpand raw
| rename raw AS _raw
| rex "^(?<name>\S+)\s*(?<Marbles>Marbles)?\s*(?<Hopscotch>Hopscotch)?\s*(?<Jacks>Jacks)?\s*(?<Baseball>Baseball)?"
| table name Marbles Hopscotch Jacks Baseball

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eval Games=mvappend(Marbles, Hopscotch, Jacks, Baseball)
| nomv Games
| rex field=Games mode=sed "s/[\r\n]+/|/g"

[scottrunyon]

Woodcock,

I added an eval ltrim and rtrim because the rex added the "|" to the end of the line and if the "Marbles" field was empty, a "|" was the first character.

Thank you for the answer.

Scott

[tomasmoser]

I liked that one with ltrim !!! Saved me 🙂