Extracting text in confluence table toolbox SQL

Bill Howard

Hi.  I'm trying to create some sql to easily determine if a value in one of my table columns contains a valid number.  My first attempt is to use the SUBSTRING function to extract the number from the string in the column, but I can't get it to work.

The column I'm trying to access is in table 1 in my macro - here's a working select statement that displays the column as 'P1 Valuation'

SELECT T1.'Phase 1 Value: Initial' as 'P1 Value',

The data in this column has the format of $n.nm

I'd like to extract just the number portion of this so I can use it to basically confirm that a valid number has been entered as part of the string, so I'm trying this...

SUBSTRING(T1.'Phase 1 Valuation: Initial',2,3) as 'P1 Number',

This isn't working though.  I also tried this:

 

SUBSTRING(T1.'P1 Value',2,3) as 'P1 Number',

 

I completely admit to being a novice with sql, so as a second part of my question, I'd love to get a link to some documentation on all of the functions available in the confluence table toolbox sql.

Thanks in advance!

Stiltsoft support

Hi @Bill Howard ,

Seems that you are talking about our app - Table Filter, Charts & Spreadsheets for Confluence.

So, firstly we need to say that the Table Toolbox macro is used to wrap different macros one into another to get precise custom results.

Confluence Cloud doesn't let us nest macros directly on the page. So, you wrap your table in the Table Toolbox macro and create macro sequences inside its shell. For example, you may wrap your table in the Table Filter macro, then in the Table Transformer macro and finally in the Chart from Table macro to visualize your filtered and transformed table.

But the mentioned Table Transformer macro really operates with SQL and we have a big part of our documentation related to this macro (how to add it, what functions are supported, step-by-step examples of use cases and so on).

For example, as I understand from your description, you need to extract "numbers-delimiter-numbers" parts from your strings. The MATCH_REGEX function may help: https://docs.stiltsoft.com/tfac/cloud/using-regular-expressions-in-sql-188618448.html#UsingregularexpressionsinSQL-Searchinginthestringwitharegularexpression

In the example our delimiter is "/" but you can adjust the regular expression for your needs. To learn more about regular expressions, you may check this guide.

If you are stuck and need to share your real data that is confidential, you may refer to our support portal.

And if I've gotten you wrong and you use some other app with similar macro names, please define what exactly is installed for your instance.