position of a character in the string

[Rachi Manwal]

I was trying to find the position of a character from a string but didn't find any function for the same. Like, in SQL we have charIndex and in MySql there is Position function

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @Rachi Manwal ,

Please clarify what app/macro are you using for the case?

If we are talking about our Table Filter and Charts for Confluence app and its Table Transformer macro, you may try the following query:

SELECT *,
T1.'String to find'->indexOf("new") AS 'Position'
FROM T1

[Rachi Manwal]

Thank you @Katerina Kovriga _Stiltsoft_ , I am using the Table Transformer macro. Could you let me know any resource where I could find these functions. I am a bit confused, is this not totally sql?

[Katerina Kovriga _Stiltsoft_]

Yes, you are right - it's a bit of JavaScript provided by our developers.

The JavaScript methods in general are documented here, but if you have any specific case that is not very common and is not documented, you may raise a support request (the portal is confidential), attach a screenshot of your original table with a visible header row and describe what you need to achieve - our engineers will provide you with a corresponding query.

[Rachi Manwal]

okay, thank you!!

[Katerina Kovriga _Stiltsoft_]

Hi @Rachi Manwal ,

Another hint from our developers - in the AlaSQL query on which our Table Transformer macro is based, it's INSTR function:

SELECT *,
INSTR(T1.'String to find',"new") AS 'Position'
FROM T1

The output for my example table will be 11 and 16 respectively. Here you may correct the output by +/-1: the indexOf function begins to count from 0 (so we got 10 and 15), the INSTR function - from 1. So here comes the difference.