Table Transformer is powered by the AlaSQL library. Find the list of SQL functions available in the macro below.

Autocompletion


Use autocompletion for correct reference to column names, SQL functions and quick writing of SQL queries: start typing, e.g.,  T1.  and choose the column; or a function name to pick from the dropdown.



General SQL syntax 

Syntax typeDefinitionCharacter(s)Example
Table nameA title given to (a) table(s) placed inside Table Transformer in the order of placementT* - for all the tables in the macro body,
T1 - for the first table,
T2 - for the second one,
etc.

SELECT 'Column' FROM T*

SELECT 'Column name' FROM T1

SELECT 'Column Name' FROM T2

Column name

The title of a column from the source table utilized in the SQL query

Can be paired with Table name as a direct reference for a number of purposes: e.g. if column names  in T1 and T2 match

'  ' - Single quotes,

[  ] - Square brackets

'Column name'

[Column name]


T1.'Same Column name'

T2.'Same Column name'


If a column name contains a s ingle quote as its part: e.g. D'column, type \ before it to avoid the single quote becoming syntax markup: e.g. 'D\'column', [D\'column] or T1.'D\'column' - in the SQL query


String constantEncloses some text between double quotes further utilized in the SQL query" " - Double quotes

"Some constant"

"Free text"

Number constantEncloses a number further utilized in the SQL queryNo additional markup

2077


Date constantEncloses a date between double quotes further utilized in the SQL query" " - Double quotes"25.05.2022"
Multi-line comments

The text included directly in the SQL query as clarification, new info, etc., but not displayed in the macro output

/*  */ - Slash + Asterisk

/* Such comment is present directly in the SQL query but not included in the output */

Functions and keywords



Function / KeywordTypeDescription
ABS

Returns the absolute (positive) value of a number
ALL

Returns true if all of the subquery values meet the condition
AND

Includes rows where all the conditions separated by AND are TRUE
ANY

Returns true if any of the subquery values meet the condition
ARRAY

Creates an array
AS

Renames a column or table with an alias 
ASC

 

 

Ascending sorting order
AT


AVG

 

Returns the average value of a group
BEGIN

 

 

Starts code block for WHILE and IF statements
BETWEEN

Selects values within a given range
BY

 

 

Used with ORDER BY, GROUP BY
CASE

 

 

Creates different outputs based on conditions (see an example)
CAST

 

 

Converts a value (of any type) into a specified datatype
CEIL

Gets the smallest integer which is greater than, or equal to, the specified numeric expression
CHAR

Returns the character with the specified integer unicode
CHECK

Limits the value that can be placed in a column
COALESCE

Returns the first non-null value in a list
CONCAT

Concatenates values
CONCAT_VIEW

Concatenates values, preserving their original HTML format
CONCAT_VIEW_AGGR

 

Concatenates and aggregates values, preserving their original HTML format
CORRESPONDING

Matches the columns in table-expressions by name and not by ordinal position
COUNT

 

 

Returns the number of rows that matches a specified criteria
CROSS

One of the Join types. Returns all rows for all possible combinations of two tables
CUBE

 

 

Generates subtotals for all combinations of grouping columns specified in the GROUP BY clause
CURRENT_TIMESTAMP

 

 

Returns the current timestamp of the operating system of the server on which the SQL Server Database runs
DATE

 

 

A data type for storing a date (string like 'YYYYMMDD')
DATE_ADD

 

 

Adds time values (as intervals) to a date value
DATE_SUB

 

 

Subtracts a time/date interval from a date and then returns the date
DATEADD

 

Adds time values (as intervals) to a date value
DATEDIFF

 

 

Returns the difference between two dates
DAY

 

 

 

Returns the day of the month for a date
DAYOFWEEK

 

 

Returns the weekday index for a date


DEFAULT

 

 

Provides a default value for a column
DESC

 

 

Descending sorting order
DISTINCT

 

 

Selects only distinct (different) values
ELSE

Introduces another statement that is executed when the IF condition is not satisfied
END

Completes code block for WHILE and IF statements
ESCAPE

 

 

Equals to LIKE
ESCAPEMARKUP

Escapes special characters for wiki markup
EXCEPT

Combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement
EXISTS

Tests for the existence of any record in a subquery
EXP

Returns e raised to the power of the specified number
FALSE

Logical false value
FIRST

Returns the first value in the group
FLOOR

Returns the largest integer value that is smaller than or equal to a number
FORMATDATE

 

 

Converts dates to the specified in the Table Transformer macro settings date format: FORMATDATE (calculated_date, date_format_to_specify <- optional argument)
FORMATMARKDOWN

Applies Markdown syntax to table cells
FORMATNUMBER

 

Format numbers according to the settings in the Table Transformer macro: FORMATNUMBER (calculated_number, decimal_separator, thousands_separator, decimal_places <- optional arguments used simultaneously)
FORMATTIME

 

 

Converts time to the specified in the Table Transformer macro settings time format to the calculated time: FORMATTIME (calculated_time)
FORMATWIKI

Applies Confluence Wiki Markup to table cells
FORMATWORKLOG

 

 

Converts worklogs to the specified in the Table Transformer macro worklog settings: FORMATWORKLOG (calculated_worklog)
FROM

Specifies which table to select or delete data from
FULL

FULL OUTER JOIN: returns all rows when there is a match in either left table or right table
GETDATE

 

Returns the current system date and time
GREATEST

 

 

Returns the greatest value of the list of arguments
GROUP

Groups the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG)
GROUPING

 

 

Indicates whether the specified column in a GROUP BY Clause aggregated or not
HAVING

 

 

Used instead of WHERE with aggregate functions
HOUR

 

 

Returns the hour part of a datetime
IF

Conditional statement
IFNULL

Returns a specified value if the expression is NULL
IIF

Accepts three arguments. It evaluates the first argument and returns the second argument if the first argument is true; otherwise, it returns the second argument
IN

Allows you to specify multiple values in a WHERE clause

INNER

INNER JOIN: returns rows that have matching values in both tables
INSERT

Inserts new records into a table (see an example)
INSTR

Returns the position of the first occurrence of a string in another string
INTERSECT

Intersects two SELECT results
INTERVAL

Defines a date/time interval
IS


ISNULL

Returns TRUE or FALSE depending on whether an expression is NULL

JOIN

Joins tables (see an example)
LAST

Returns the last value of the selected column
LCASE

Converts a string to lower-case
LEAST

 

 

Returns the smallest value of the list of arguments
LEFT

LEFT JOIN: returns all rows from the left table, and the matching rows from the right table
LENGTH

Returns the length of a string (in bytes)
LIKE

 

 

Searches for a specified pattern in a column
LIMIT

Limit the number of records from the result set
LOG

Returns the natural logarithm
MATCH_REGEXP

 

Allows to add flags (i.e. g (global), i (insensitive), etc.) to regular expressions: MATCH_REGEXP ('Column1',"[a-f]{6}","g")
MAX

 

 

Takes the name of a column as an argument and returns the largest value in that column
MEDIAN

 

 

Returns the median value of a group (see an example)
MID

Extracts a substring from a string (starting at any position)
MIN

 

 

Takes the name of a column as an argument and returns the smallest value in that column
MINUTE

 

 

Returns the minute part of a datetime value
MONTH

 

 

Returns the month part of a date
MULT

 

Takes the name of a column as an argument and returns multiplying of all the values in that column

NATURAL

NATURAL JOIN: analyzes columns in two joined tables and makes a join by columns with similar names
NOT

Includes rows where a condition is not TRUE
NOW

Returns current date and time
NULL

A field with no value
NULLIF

Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned
OF

Allows you to specify multiple values in a WHERE clause
OFFSET

Skips the first N rows in a result set before starting to return any rows
ON


OR

Includes rows where either condition is TRUE
ORDER

Sorts the result set in ascending or descending order
OUT

Opposite to IN
OUTER

OUTER JOIN: returns all rows when there is a match in either left table or right table
PERCENT

Indicates that the query returns N percentage of rows
PIVOT

Converts data from row level to column level

POWER

Returns the value of a number raised to the power of another number
QUART

 

Returns the first quartile of a group
QUART2

 

Returns the second quartile of a group
QUART3

 

Returns the third quartile of a group
RANDOM


REGEXP_LIKE

 

 

 

Returns rows that match a regular expression pattern
REPLACE

Replaces all occurrences of a substring within a string, with a new substring
REPLACE_VIEW

Replaces all occurrences of a substring within a string, with a new substring, preserving its original HTML format
RIGHT

RIGHT JOIN: returns all rows from the right table, and the matching rows from the left table
ROLLUP

 

 

Creates subtotals and grand totals for a set of columns (GROUP BY ROLLUP)
ROUND

Rounds a number to a specified number of decimal places
ROWNUM

Adds row numbering to a table
SEARCH

Searches for a specified pattern (see an example)
SECOND

 

 

Return the seconds part of a datetime value
SELECT

Selects data from a database
SOME

Compares a value to each value in a list or results from a query and evaluates to TRUE if the result of an inner query contains at least one row
SQRT

Returns the square root of a number
STDDEV

 

 

Returns the population standard deviation of expression (see an example)
STDEV

 

 

Returns the sample standard deviation of expression (see an example)
SUBDATE

 

 

Subtracts a time/date interval from a date and then returns the date
SUBSTR

Extracts a substring from a string (starting at any position)
SUBSTRING

Allows to extract a substring from a string
SUBSTRING_VIEW

Allows to extract a substring from a string, preserving its original HTML format
SUM

 

 

Takes the name of a column as an argument and returns the sum of all the values in that column (see an example)
THEN

A result expression in a search condition
TIME

 

 

Extracts the time part from a given time/datetime

Returns a datetime value based on a date or datetime value

TO


Removes the space character or other specified characters from the start or end of a string
TRUE

Logical true value
UCASE

Converts a string to upper-case
UNION

Combines the result set of two or more SELECT statements (only distinct values)
UPDATE

Modifies the existing records in a table (see an example)
UPPER

Converts a string to upper-case
USING

Matches only one column when more than one column matches
VAR

 

 

Returns the statistical variance of all values in the specified expression
VAR_POP

Population variance. Returns 0 if all of the values in the data set have the same value (no variability). Returns 0 if the data set consists of only one value (no possible variability). Returns NULL if the data set has no values
VAR_SAMP

Sample variance. Returns 0 if all of the values in the data set have the same value (no variability). Returns NULL if the data set consists of only one value (no possible variability). Returns NULL if the data set has no values
VARCHAR

Accepts character strings of a variable length is up to the maximum length specified in the data type declaration
VARP

Returns the Variance for the population of the total number of records present in the specified column
WEEKDAY

 

 

Returns the weekday number for a given date
WHEN

A condition expression in a search condition
WHERE

 

 

Filters a result set to include only records that fulfill a specified condition
WHILE

Sets a condition for the repeated execution of an SQL statement or statement block
YEAR

 

 

Returns an integer value which represents the year of the specified date
TIMESTAMPDIFF

 

 

Returns the difference between two dates
TEXT

Returns the source string without additional formatting
HTML_DECODE

Converts the displayed string into HTML markup and extracts text content from it


Conversion types

For date conversion see also the FORMATDATE, FORMATTIME, FORMATWORKLOG functions.

JavaScript methods

After converting to date you can use JavaScript date functions, for example:

'Column with dates'::Date→toDateString()

Find an example here.

After converting to string you can use JavaScript string functions, for example:

'Column with strings'::string->split("The delimiter")->0

'Column with strings'::string→length

After converting to number you can use JavaScript number functions, for example:

'Column with numbers'::number→toFixed(1)


You need to convert only values with a different or indefinite type. In other cases, it's is not necessary:

'Column with numbers'->toFixed(1)


Example

The source table:

The result

SQL query:

SELECT 
T1.'String'->length AS 'Length',
T1.'String'->split("b")->0 AS 'Splitting',
T1.'Number'->toFixed(1) AS 'Rounding to tenth',
T1.'Date'::Date->getFullYear() AS 'Full year'
FROM T*