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.

[Stiltsoft support]

Not sure how your original table looks like but an example for you:

Then you go to the macro settings and set the delimiters:

Insert the following SQL query:

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

And get the following result:

Hope it somehow helps.

[Bill Howard]

Okay - thanks!  I'm getting somewhere with this!  I was able to use the MATCH_REGEX function to create a column with the number formats I'm looking for.  

How would I use the results of the MATCH_REGEX function so the query only returns the rows that have a match?  What I'm trying to do is as simple as returning all of the rows in the table where the MATCH_REGEX function produces a non-null result.

[Stiltsoft support]

Hi @Bill Howard ,

You may use the Table Filter macro on top of the Table Transformer and set the reversed filters for the 'New Number' column (filter empty values and then inverse the filter to get only not empty results). Then you may hide the filtration panel or leave it as it is so that users will be able to filter your table on the fly. https://docs.stiltsoft.com/tfac/cloud/table-filtration-42239755.html#Tablefiltration-Filteringrowsnotequaltoselectedvalues

Or you may add a filtration condition inside the SQL query:

SELECT *,
MATCH_REGEXP(T1.'Number', "\d+", "g")
AS 'New Number'
FROM T*
WHERE MATCH_REGEXP(T1.'Number', "\d+", "g") NOT NULL