Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

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

Dolly Kirubavathi February 28, 2024

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!

1 answer

2 votes
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 28, 2024

Hi @Dolly Kirubavathi ,

You may try smth like this:

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

Wed 6-1.png

Hope it helps your case.

Dolly Kirubavathi February 28, 2024

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 28, 2024

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 February 28, 2024

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 28, 2024

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:

Wed 7-1.png

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

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

Wed 7-2.png

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

Wed 7-4.png

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

Wed 7-3.png

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
TAGS
AUG Leaders

Atlassian Community Events