split does not recognize a comma as a delimiter for an array of numbers that has been converted

[Henry Torres]

Hello! 

Looking for some assistance with the split function not recognizing a comma as a delimiter for an array of numbers.


I found the following SQL from the examples linked below: 

SEARCH / AS @a EX('Col 2'->split(",")) /
RETURN(@a->'Col 1' AS 'Col 1', _ AS 'Col 2') FROM T1

This will not work on the following table because they are all numeric characters

Col 1	Col 2
Row 1	111. 222
Row 2	333
Row 3	444, 555

if you proceed to modify the SQL to the following by converting 'Col 2' to strings, it still wont work  

SEARCH / AS @a EX('Col 2'::string->split(",")) /
RETURN(@a->'Col 1' AS 'Col 1', _ AS 'Col 2') FROM T1

The only way for this SQL to work is to enter a "character" into the values like so - notice the ' before the 111: 

Col 1	Col 2
Row 1	'111. 222
Row 2	333
Row 3	444, 555

My workaround to this issue is the following modifications to both SQL and data - i change from <,> to <;> and it will work. 

SEARCH / AS @a EX('Col 2'::string->split(";")) /
RETURN(@a->'Col 1' AS 'Col 1', _ AS 'Col 2') FROM T1

Col 1	Col 2
Row 1	111; 222
Row 2	333
Row 3	444; 555

Research:

Flatten an array in Table transformer macro 

Splitting cell values in a column to different rows 

[Stiltsoft support · Accepted solution]

Hi @Henry Torres ,

Seems that you are talking about our Table Filter, Charts & Spreadsheets for Confluence app and its Table Transformer macro.

Sometimes if we are talking about numbers, "," and "." can be mixed up with thousands or decimal delimiters, so you may adjust the first query from your post as

SEARCH / AS @a EX(TEXT('Col 2')->split(";")) /
RETURN(@a->'Col 1' AS 'Col 1', _ AS 'Col 2') FROM T1

Here we tell the macro to treat the cells' contents as text (not numbers or dates).

[Henry Torres]

that did the trick! using the TEXT annotation and <, comma> as a delimiter gave me the correct output

thank you very much!

any additional recommendations on how to get the output to format correctly if a cell only had one value? 

For example:

Source Table

Col 1	Col 2
Row 1	111, 222
Row 2	333
Row 3	444, 555

Output Table

[Stiltsoft support]

This row wasn't modified, so as the source table had the left align, such orientation was preserved for the untouched cells.

You may use the right align for the source table or add another Table Transformer macro on top of the first one with the FORMATWIKI function: https://docs.stiltsoft.com/tfac/cloud/changing-alignment-188620085.html

[Stiltsoft support]

Please note that as you are on Cloud, you'll need the Table Toolbox macro to nest macros. This macro was specifically developed to overcome the new Cloud editor limitations.

[Henry Torres]

thank you very much for all the feedback it has been extremely helpful