General syntax and SQL functions available

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

Custom SQL query editor powered by AI

Use autocompletion for correct reference to column names and SQL functions:

Start typing, for example, "T1." and choose the required column from the list. It helps to avoid typos
Start typing any function name and pick what you need from the dropdown list of supported functions/keywords

The AI also enhances the SQL query editor and suggests variants of the possible queries while you are typing. The options change on the fly according to what you have already typed.

If the suggestion suits you, press Tab on your keyboard and the query will be inserted in the editor.

And if you use the Ctrl+F shortcut, the "Find and Replace" panel will be opened. It's handy when you copy/paste your queries and need to change only some parts.

General SQL syntax

Syntax type	Definition	Character(s)	Example
Table name	A title given to (a) table(s) placed inside Table Transformer in the order of placement	T* - 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 constant	Encloses some text between double quotes further utilized in the SQL query	" " - Double quotes	"Some constant" "Free text"
Number constant	Encloses a number further utilized in the SQL query	No additional markup	2077
Date constant	Encloses 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 / Keyword	Type	Description
1	ABS	MATH	Returns the absolute (positive) value of a number
2	ALL	LOGIC	Returns true if all of the subquery values meet the condition
3	AND	LOGIC	Includes rows where all the conditions separated by AND are TRUE
4	ANY	LOGIC	Returns true if any of the subquery values meet the condition
5	ARRAY	DATA_MANIPULATING	Creates an array
6	AS	DATA_MANIPULATING	Renames a column or table with an alias
7	ASC	MATH ORDERING	Ascending sorting order
8	AT	TIME
9	AVG	MATH AGGREGATION	Returns the average value of a group
10	BEGIN	LOGIC LOOP	Starts code block for WHILE and IF statements
11	BETWEEN	MATH	Selects values within a given range
12	BY	AGGREGATION ORDERING	Used with ORDER BY, GROUP BY
13	CASE	DATA_MANIPULATING LOGIC	Creates different outputs based on conditions (see an example)
14	CAST	DATA_MANIPULATING CONVERTING	Converts a value (of any type) into a specified datatype
15	CEIL	MATH	Gets the smallest integer which is greater than, or equal to, the specified numeric expression
16	CHAR	STRING_OPERATORS	Returns the character with the specified integer Unicode
17	CHECK	LOGIC	Limits the value that can be placed in a column
18	COALESCE	LOGIC	Returns the first non-null value in a list (see an example)
19	CONCAT	MERGING	Concatenates values (see an example)
20	CONCAT_VIEW	MERGING	Concatenates values, preserving their original HTML format (see an example)
21	CONCAT_VIEW_AGGR	MERGING AGGREGATION	Concatenates and aggregates values, preserving their original HTML format (see an example)
22	CORRESPONDING	MERGING	Matches the columns in table-expressions by name and not by ordinal position
23	COUNT	MATH AGGREGATION	Returns the number of rows that matches a specified criteria (see an example)
24	CROSS	MERGING	One of the Join types. Returns all rows for all possible combinations of two tables
25	CUBE	MATH AGGREGATION	Generates subtotals for all combinations of grouping columns specified in the GROUP BY clause
26	CURRENT_TIMESTAMP	VARIABLES TIME	Returns the current timestamp of the operating system of the server on which the SQL Server Database runs
27	DATE	DATA_MANIPULATING CONVERTING	A data type for storing a date (string like 'YYYYMMDD')
28	DATE_ADD	MATH TIME	Adds time values (as intervals) to a date value
29	DATE_SUB	MATH TIME	Subtracts a time/date interval from a date and then returns the date
30	DATEADD	MATH TIME	Adds time values (as intervals) to a date value (see an example)
31	DATEDIFF	MATH TIME	Returns the difference between two dates (see an example)
32	DAY	DATA_MANIPULATING TIME CONVERTING	Returns the day of the month for a date
33	DAYOFWEEK	DATA_MANIPULATING TIME	Returns the weekday index for a date
34	DEFAULT	DATA_MANIPULATING LOGIC	Provides a default value for a column
35	DESC	MATH ORDERING	Descending sorting order
36	DISTINCT	LOGIC AGGREGATION	Selects only distinct (different) values (see an example)
37	ELSE	LOGIC	Introduces another statement that is executed when the IF condition is not satisfied
38	END	LOGIC	Completes code block for WHILE and IF statements
39	ESC ESCAPE APE	LOGIC SEARCHING	Used with LIKE, escapes characters
40	ESCAPEMARKUP	STRING_OPERATORS	Escapes special characters for wiki markup (see an example)
41	EXCEPT	MERGING	Combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement
42	EXISTS	LOGIC	Tests for the existence of any record in a subquery (see an example)
43	EXP	MATH	Returns e raised to the power of the specified number
44	FALSE	LOGIC	Logical false value
45	FIRST	AGGREGATION	Returns the first value in the group
46	FLOOR	MATH	Returns the largest integer value that is smaller than or equal to a number
47	FORMATDATE	DATA_MANIPULATING TIME	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)
48	FORMATMARKDOWN	FORMATTING	Applies Markdown syntax to table cells (see an example)
49	FORMATNUMBER	DATA_MANIPULATING FORMATTING	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)
50	FORMATTIME	DATA_MANIPULATING TIME	Converts time to the specified in the Table Transformer macro settings time format to the calculated time: FORMATTIME (calculated_time)
51	FORMATWIKI	FORMATTING	Applies Confluence Wiki Markup to table cells (see an example)
52	FORMATWORKLOG	DATA_MANIPULATING TIME	Converts worklogs to the specified in the Table Transformer macro worklog settings: FORMATWORKLOG (calculated_worklog) (see an example)
53	FROM	KEYWORD	Specifies which table to select or delete data from
54	FULL	MERGING	FULL OUTER JOIN: returns all rows when there is a match in either left table or right table
55	GETDATE	DATA_MANIPULATING TIME	Returns the current system date and time
56	GREATEST	MATH AGGREGATION	Returns the greatest value of the list of arguments
57	GROUP	AGGREGATION	Groups the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG)
58	GROUPING	LOGIC AGGREGATION	Indicates whether the specified column in a GROUP BY Clause aggregated or not
59	HAVING	LOGIC AGGREGATION	Used instead of WHERE with aggregate functions
60	HOUR	DATA_MANIPULATING TIME	Returns the hour part of a datetime
61	HTML_DECODE	STRING_OPERATORS	Converts the displayed string into HTML markup and extracts text content from it
62	IF	LOGIC	Conditional statement
63	IFNULL	LOGIC	Returns a specified value if the expression is NULL
64	IIF	LOGIC	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
65	IN	LOGIC	Allows you to specify multiple values in a WHERE clause
66	INNER	MERGING	INNER JOIN: returns rows that have matching values in both tables
67	INSERT	DATA_MANIPULATING	Inserts new records into a table (see an example)
68	INSTR	STRING_OPERATORS	Returns the position of the first occurrence of a string in another string
69	INTERSECT	MERGING	Intersects two SELECT results
70	INTERVAL	TIME	Defines a date/time interval
71	IS	LOGIC
72	ISNULL	LOGIC	Returns TRUE or FALSE depending on whether an expression is NULL
73	JOIN	MERGING	Joins tables (see an example)
74	LAST	DATA_MANIPULATING	Returns the last value of the selected column
75	LCASE	STRING_OPERATORS	Converts a string to lower-case
76	LEAST	MATH AGGREGATION	Returns the smallest value of the list of arguments
77	LEFT	MERGING	LEFT JOIN: returns all rows from the left table, and the matching rows from the right table
78	LENGTH	STRING_OPERATORS	Returns the length of a string (in bytes)
79	LIKE	LOGIC SEARCHING	Searches for a specified pattern in a column
80	LIMIT	SEARCHING	Limit the number of records from the result set
81	LOG	MATH	Returns the natural logarithm
82	MATCH_REGEXP	DATA_MANIPULATING LOGIC SEARCHING	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)
83	MAX	MATH AGGREGATION	Takes the name of a column as an argument and returns the largest value in that column
84	MEDIAN	MATH AGGREGATION	Returns the median value of a group (see an example)
85	MID	STRING_OPERATORS	Extracts a substring from a string (starting at any position)
86	MIN	MATH AGGREGATION	Takes the name of a column as an argument and returns the smallest value in that column
87	MINUTE	DATA_MANIPULATING TIME	Returns the minute part of a datetime value
88	MONTH	DATA_MANIPULATING TIME	Returns the month part of a date
89	MULT	MATH AGGREGATION	Takes the name of a column as an argument and returns multiplying of all the values in that column
90	NATURAL	MERGING	NATURAL JOIN: analyzes columns in two joined tables and makes a join by columns with similar names
91	NOT	LOGIC	Includes rows where a condition is not TRUE
92	NOW	VARIABLES	Returns current date and time
93	NULL	LOGIC	A field with no value
94	NULLIF	LOGIC	Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned
95	OF	LOGIC	Allows you to specify multiple values in a WHERE clause
96	OFFSET	SEARCHING	Skips the first N rows in a result set before starting to return any rows
97	ON	MERGING
98	OR	LOGIC	Includes rows where either condition is TRUE
99	ORDER	ORDERING	Sorts the result set in ascending or descending order
100	OUT	LOGIC	Opposite to IN
101	OUTER	MERGING	OUTER JOIN: returns all rows when there is a match in either left table or right table
102	PERCENT	SEARCHING	Indicates that the query returns N percentage of rows
103	PIVOT	AGGREGATION	Converts data from row level to column level (see an example)
104	POWER	MATH	Returns the value of a number raised to the power of another number
105	QUART	MATH AGGREGATION	Returns the first quartile of a group
106	QUART2	MATH AGGREGATION	Returns the second quartile of a group
107	QUART3	MATH AGGREGATION	Returns the third quartile of a group
108	RANDOM	VARIABLES
109	REGEXP_LIKE	LOGIC STRING_OPERATORS SEARCHING	Returns rows that match a regular expression pattern
110	REGEXP_REPLACE	LOGIC STRING_OPERATORS SEARCHING	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)
111	REPLACE	STRING_OPERATORS	Replaces all occurrences of a substring within a string with a new substring
112	REPLACE_VIEW	STRING_OPERATORS	Replaces all occurrences of a substring within a string with a new substring and preserves the original HTML format (see an example)
113	RIGHT	MERGING	RIGHT JOIN: returns all rows from the right table, and the matching rows from the left table
114	ROLLUP	MATH AGGREGATION	Creates subtotals and grand totals for a set of columns (GROUP BY ROLLUP)
115	ROUND	MATH	Rounds a number to a specified number of decimal places
116	ROWNUM	DATA_MANIPULATING	Adds row numbering to a table (see an example)
117	SEARCH	DATA_MANIPULATING	Searches for a specified pattern (see an example)
118	SECOND	DATA_MANIPULATING TIME	Return the seconds part of a datetime value
119	SELECT	DATA_MANIPULATING	Selects data from a database
120	SOME	LOGIC	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
121	SPLIT	DATA_MANIPULATING STRING_OPERATORS	Splits a string into an array of substrings using a separator (see an example)
122	SPLIT_VIEW	DATA_MANIPULATING STRING_OPERATORS	Splits a string into an array of substrings using a separator and preserves the original HTML format (see an example)
123	SQRT	MATH	Returns the square root of a number
124	STDDEV	MATH AGGREGATION	Returns the population standard deviation of expression (see an example)
125	STDEV	MATH AGGREGATION	Returns the sample standard deviation of expression (see an example)
126	SUBDATE	MATH TIME	Subtracts a time/date interval from a date and then returns the date
127	SUBSTR	STRING_OPERATORS	Extracts a substring from a string (starting at any position)
128	SUBSTRING	STRING_OPERATORS	Allows to extract a substring from a string
129	SUBSTRING_VIEW	STRING_OPERATORS	Allows to extract a substring from a string and preserves the original HTML format (see an example)
130	SUM	MATH AGGREGATION	Takes the name of a column as an argument and returns the sum of all the values in that column (see an example)
131	TEXT	STRING_OPERATORS	Formats the source data as string and returns it without additional formatting (see an example)
132	THEN	LOGIC	A result expression in a search condition
133	TIME	DATA_MANIPULATING TIME	Extracts the time part from a given time/datetime
134	TIMESTAMP	CONVERTING	Returns a datetime value based on a date or datetime value
135	TIMESTAMPDIFF	MATH TIME	Returns the difference between two dates
136	TO	KEYWORD
137	TRIM	STRING_OPERATORS	Removes the space character or other specified characters from the start or end of a string
138	TRUE	LOGIC	Logical true value
139	UCASE	STRING_OPERATORS	Converts a string to upper-case
140	UNION	MERGING	Combines the result set of two or more SELECT statements (only distinct values)
141	UNPIVOT	AGGREGATION	Converts data from column level to row level, ungroups data (see an example)
142	UPDATE	DATA_MANIPULATING	Modifies the existing records in a table (see an example)
143	UPPER	STRING_OPERATORS	Converts a string to upper-case
144	USING	LOGIC	Matches only one column when more than one column matches
145	VAR	MATH AGGREGATION	Returns the statistical variance of all values in the specified expression (see an example)
146	VAR_POP	AGGREGATION	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
147	VAR_SAMP	AGGREGATION	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
148	VARCHAR	KEYWORD	Accepts character strings of a variable length is up to the maximum length specified in the data type declaration
149	VARP	AGGREGATION	Returns the Variance for the population of the total number of records present in the specified column (see an example)
150	WEEKDAY	DATA_MANIPULATING TIME	Returns the weekday number for a given date
151	WHEN	LOGIC	A condition expression in a search condition
152	WHERE	LOGIC SEARCHING	Filters a result set to include only records that fulfill a specified condition
153	WHILE	LOOP	Sets a condition for the repeated execution of an SQL statement or statement block
154	YEAR	DATA_MANIPULATING TIME	Returns an integer value which represents the year of the specified date

Conversion types

::Date
::string
::number

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()

CODE

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

CODE

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

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

CODE

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)

CODE

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*

SQL

General syntax and SQL functions available

Custom SQL query editor powered by AI

General SQL syntax

Functions and keywords

Conversion types

JavaScript methods

Example

Download PDF

Cookie Notice