| Function / Keyword | Type | Description |
|---|
| 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 (see an example) |
| CONCAT | | Concatenates values (see an example) |
| CONCAT_VIEW | | Concatenates values, preserving their original HTML format (see an example) |
| CONCAT_VIEW_AGGR | | Concatenates and aggregates values, preserving their original HTML format (see an example) |
| 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 (see an example) |
| 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 (see an example) |
| DATEDIFF | | Returns the difference between two dates (see an example) |
| 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 (see an example) |
| ELSE | | Introduces another statement that is executed when the IF condition is not satisfied |
| END | | Completes code block for WHILE and IF statements |
| ESCESCAPEAPE | | Used with LIKE, escapes characters |
| ESCAPEMARKUP | | Escapes special characters for wiki markup (see an example) |
| 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 (see an example) |
| 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 major date formats(accordingly parsed by the macro, e.g. 17 Mar 2024) to either specified in the Table Transformer settings date format or the one set in the optional argument: FORMATDATE(Your "Date"/'Column name here',"mm/dd/y" <- optional argument) =
FORMATDATE("17 Mar 2024", "mm/dd/y") → 03/17/24 (see an example) |
| FORMATMARKDOWN | | Applies Markdown syntax to table cells (see an example) |
| FORMATNUMBER | | Format numbers (accordingly parsed by the macro, e.g. 14) to either the settings specified in the Table Transformer macro or those corresponding to the optional arguments set: FORMATNUMBER (Your "Number"/'Column name here', "decimal_separator"(e.g. "," or "."), "thousands_separator"(e.g. "," or "."), decimal_places (e.g. 2) <-three optional arguments used simultaneously) = FORMATNUMBER(1400, ",", ".", 2) → 1.400,00 (see an example) |
| 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 (see an example) |
| FORMATWORKLOG | | Converts worklogs to the specified in the Table Transformer macro worklog settings: FORMATWORKLOG (calculated_worklog) (see an example) |
| 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 |
| HTML_DECODE | | Converts the displayed string into HTML markup and extracts text content from it |
| 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 | | Searches for a regular expression in the input string and allows to add flags (i.e. g (global), i (insensitive), etc.) to regular expressions: MATCH_REGEXP ('Column1',"[a-f]{6}","g") (see an example) |
| 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 (see an example) |
| 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 |
| REGEXP_REPLACE | | Returns a modified version of the source string where occurrences of the regular expression pattern found in the source string are replaced with the specified replacement string (see an example) |
| 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 and preserves the original HTML format (see an example) |
| 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 (see an example) |
| 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 |
| SPLIT | | Splits a string into an array of substrings using a separator (see an example) |
| SPLIT_VIEW | | Splits a string into an array of substrings using a separator and preserves the original HTML format (see an example) |
| 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 and preserves the original HTML format (see an example) |
| SUM | | Takes the name of a column as an argument and returns the sum of all the values in that column (see an example) |
| TEXT | | Formats the source data as string and returns it without additional formatting (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 |
| TIMESTAMPDIFF | | Returns the difference between two dates |
| |
|
| | 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) |
| UNPIVOT | | Converts data from column level to row level, ungroups data (see an example) |
| 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 (see an example) |
| 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 (see an example) |
| 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 |
