Hello all,
I am working with join expressions under the ‘String Manipulation’ node.
I would like to be able to concatenate strings, however, if one of the columns’ value its been used to concatenate has spaces, I must use “double quotes” to handle that specific case.
For example:
color |
carName |
black |
Jaguar |
blue |
BMW |
white |
Jeep Grand Cherokee |
Output:
black Jaguar
blue BMW
white “Jeep Grand Cherokee”
“I understand about the difference between brands and names of vehicles, but you get the point…”
Handling the space:
I thought of using the ‘Rule Engine’ node but can’t figure out if in it has an expression to handle IF FOUND like in Excel we have, ISNUMBER(SEARCH) . I have tried:
< " " IN $carName" => TRUE > then I append a column (spaceFound) w a true or false for later handling by the double quote function
also tried:
" " MATCHES $carName" => TRUE
both do not work
concatenating w join:
I am concatenating with join as:
join($color$, < if value is TRUE on (spaceFound) col , then > DOUBLE QUOTES,$carName$,DOUBLE QUOTES ) [adding the double quotes to the beginning and end of the value of $carName"]
Thank you all,
J.
Hi @jarviscampbell
Before jumping towards a solution, just a double check:
Is your desired output only to quote the carName column whenever it contains a space like in the Output:?
Because in your pseudo code I see you doing join($color$,
so I’m assuming the desired output is actually one string saying white “Jeep Grand Cherokee”
Thanks
Hi @ArjenEX,
No, it will output regardless, but when there’s a space, double quotes should be added.
Thanks for checking.
J.
Sorry, I have adjusted the output as it should be.
Just need to join the strings from both columns.
Allright @jarviscampbell
My suggestion would be to use one the most powerful tools available for strings, the Column Expression.
Your desired output is achievable through:
if (contains(column("carName")," ") == true) {
column("color") + " \"" + column("carName") + "\""
} else {
column("color") + " " + column("carName")
}
What it does:
- It checks if the carName contains a space, resulting in true or false.
- If true, create a string by concatenating: the color, a quote, the carName and another quote. Key here is that you have to escape the quote since it’s a special character. This is done with the backslash.
- If false, concat only the color and carName with a space in between.
See WF:
Handling “Double Quotes” on string joining function.knwf (12.8 KB)
Hope this helps!
3 Likes
Outstanding @ArjenEX , Beautiful brain of yours. That’s exactly what I was looking for.
Again, I appreciate you.