Hi Tristan, feels in general like a problem you would rather solve in Backend using Vertica SQL than relying on PQL.

Unfortunately I am not aware of a PQL formala being capable of that in a straight forward way.

However a super messy approach I could think if of is a bunch of nested case whens. Utilizing PATINDEX() to find index of a '|' character and then processing that A and then RIGHT(index you where calculating) to removing one block after another and then processing the next one. But this would only be viable if you have a somewhat low number of concatinations. And you would have to cater for different lengths (e.g. one case if only one | is there, one case when if there are two and so forth.)

I would definetely try to solve in SQL as this is super messy and hard to maintain.

For the counting: One idea might be to just use a simple LEN() if you A,B,C values are actually of same lenght you can deduct the number of | by the number of total characters. But this is would need the above assumption.

Hi Manuel, thanks for your quick reply. I half expected that i have to do it in the backend..

Thanks also for your approach, but I don't know how many different combinations there are, so it's difficult..

I have already solved the counting part in this way: LEN("Table1"."Column1") - LEN(REPLACE("Table1"."Column1", '|', '')) + 1.

Since the number of parts is always one greater than the number of separators, the plus 1

Hi @t.m.,

Just add 'MULTI ' before your CASE WHEN statement in PQL, and it should continue to process once a match is made.

Let me know if this helped.

Best regards,

Jan-peter

Hi. Thank you! That works and i didn't knew this statement. Thanks a lot. I builded it already outside with SQL but thats a smart solution to keep in mind for the next time!