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

[Dolly Kirubavathi]

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]

Hi @Dolly Kirubavathi ,

You may try smth like this:

SELECT *,
INSTR('String', " ") AS 'Position'
FROM T*

Hope it helps your case.

[Dolly Kirubavathi]

I tried that. That did not work. May be it an Enter Character. How would I look for that?

Please help!

Thank you!

[Stiltsoft support]

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?

[Dolly Kirubavathi]

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]

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'