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 1Col 2Row 1111. 222Row 2333Row 3444, 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 1Col 2Row 1'111. 222Row 2333Row 3444, 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 1Col 2Row 1111; 222Row 2333Row 3444; 555 Research: Flatten an array in Table transformer macro Splitting cell values in a column to different rows

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Henry Torres_ejcw7s

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-&gt;'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-&gt;'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-&gt;'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

Accepted answers

Stiltsoft support

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).

All comments

Stiltsoft support

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_ejcw7s

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

cloud

confluence-cloud

filter

table

table-filter

table-filter-and-charts

table-transformer

table-filter-charts-spreadsheets