"cell splitter" vs "column expressions" : split collection x list

[Felipereis50]

Hi Friends

"I was wanting to create a string column for type ‘collection’ string, separated by pipe, and for this reason, I used ‘column expressions’.

My value is;
column0
|0200|000000000000517785|ENVELOPE SACO KRAFT NATURAL 75G PCT C 10|||PEÇ|07|||||||

Notice that for each ‘pipe’, I need to split for the collection.
The reason: I want to access the value contained in the collection based on the index.

In other words, using ‘column expressions’: Example to access by index: column0 [2]

My doubt is the following:

When I used the formula ‘split(column(“Column0”),“|”)’, within ‘column expressions’, this rule didn’t consider the split of null values. What I mean is that if I have the following value: |200|999|||XXX|, notice that there are empty separators, and the split formula disregards if they are null.

Visually, the formula worked but when I want to access index [7] for example, for some rows, it doesn’t work because as some rows have null values between the fields, the index varies.

For this reason, I had to use the ‘Cell splitter’, and use the flag as ‘As List’. By doing this, the result was correct.

Here’s the question:
How to simulate the same behavior of the cell splitter within the column expressions? (create collection, with split separator considering null values between)
Is it possible?"

[gonhaddock]

Hello @Felipereis50
You can test ‘Column Expressions’ node with the following code:

split(
    regexReplace(
        regexReplace(
            column("text"), "\\|", "\\|null")
        ,"null(\\w)", "$1")
    , "\\|")

BR

[Felipereis50]

Amazing Gordon

You’re an expert in Regex?
I studied your code , step by step, it was very clever.

I think, first you include “null” in all fields, but then you exclude the “null” again, but maintain only the null on fields that only has null. Right?

Another thing I need to learn (Regex)