Using INSTR function in Confluence Wiki Table Transformer, how do I search for a blank character?

Question

Test_String = '105 A"

I am trying to find the position of the blank character in this string. How do I code for it?

thanks!

Stiltsoft support · Answer

Are you getting this data from Jira?

It is not really a best practice to mix text and numbers together in one cell. The Table Transformer macro treats the source table as a mini SQL database, so it is better to have each number in a separate cell or at least to have a string of numbers separated by visible characters, for example, commas.

You may try the following workaround:

Now we move from the internal Table Transformer to the external one.

SELECT *,
MATCH_REGEXP(T1.'String', "\d+", "g")
AS 'Number String'
FROM T*

SEARCH / AS @a EX('Number String'->split(",")) /
RETURN(@a->'Column' AS 'Column', _ AS 'Number String') FROM T1

SELECT 'Column',
SUM('Number String') AS 'Sum of Numbers'
FROM T*
GROUP BY 'Column'

Dolly Kirubavathi · Answer

This is my real use case:

Data in real column looks like this

Test_String

105 A

4 B

5000 C

I want to extract the number out and then do a SUM of all the numbers. My end goal is to get to 105 + 5 + 5000 = 5110

Stiltsoft support · Answer

The new line is an invisible character, it doesn't count in the string length.

Maybe you can share your real use case? What data do you have in your column and what exactly you are trying to do?