Page History

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

Autocompletion

...

width	200px

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

Handy Carousel

...

Image Added

Image Removed

General SQL syntax

...

SELECT 'Column' FROM T*

SELECT 'Column name' FROM T1

SELECT 'Column Name' FROM T2

Image Added

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.

Handy Carousel

Image Added

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'

Info
If a column name contains a s ingle quote as

...

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'

...

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

Table Filter

id
totalrow	,,
hidelabels	false
ddSeparator	\u0020
sparkName	Sparkline
hidePane	Filtration panel
sparkline	false
default	,,
isFirstTimeEnter	true
cell-width	150,150,150
hideColumns	false
customNoTableMsg	false
disabled	false
enabledInEditor	false
globalFilter	true

...

1709048505344_

...

1741075635
order	0,1,2
hideControls	true
inverse	false,false,false
numbering	Dynamic Ascending
column	Type
sort	Function / Keyword ⇧
disableSave	true
separator	Point (.)
labels	Type‚Function / Keyword‚Filter whole table
ddOperator	OR
userfilter	Function / Keyword
datepattern	dd M yy
updateSelectOptions	false
worklog	365\|5\|8\|y w d h m\|y w d h m
isOR	AND

Table Excerpt

hide	true
name	STATUSES

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	LOGIC

Status

subtle	true
colour	Green
title	LOOP

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	AGGREGATION

Status

subtle	true
colour	Green
title	ORDERING

Status

subtle	true
colour	Green
title	KEYWORD

Status

subtle	true
colour	Green
title	MERGING

Status

subtle	true
colour	Green
title	VARIABLES

Status

subtle	true
colour	Green
title	STRING_OPERATORS

Status

subtle	true
colour	Green
title	TIME

Status

subtle	true
colour	Green
title	CONVERTING

Status

subtle	true
colour	Green
title	SEARCHING

Function / Keyword

Type

Description

ABS

Status

subtle	true
colour	Green
title	MATH

Returns the absolute (positive) value of a number

ALL

Status

subtle	true
colour	Green
title	LOGIC

Returns true if all of the subquery values meet the condition

AND

Status

subtle	true
colour	Green
title	LOGIC

Includes rows where all the conditions separated by AND are TRUE

ANY

Status

subtle	true
colour	Green
title	LOGIC

Returns true if any of the subquery values meet the condition

ARRAY

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Creates an array

AS

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Renames a column or table with an alias

ASC

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	ORDERING

Ascending sorting order

AT

Status

subtle	true
colour	Green
title	TIME

AVG

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	AGGREGATION

Returns the average value of a group

BEGIN

Status

subtle	true
colour	Green
title	LOGIC

Status

subtle	true
colour	Green
title	LOOP

Starts code block for WHILE and IF statements

BETWEEN

Status

subtle	true
colour	Green
title	MATH

Selects values within a given range

BY

Status

subtle	true
colour	Green
title	AGGREGATION

Status

subtle	true
colour	Green
title	ORDERING

Used with ORDER BY, GROUP BY

CASE

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	LOGIC

Creates different outputs based on conditions (see an example)

CAST

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	CONVERTING

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

CEIL

Status

subtle	true
colour	Green
title	MATH

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

CHAR

Status

subtle	true
colour	Green
title	STRING_OPERATORS

Returns the character with the specified integer

...

Unicode

CHECK

Status

subtle	true
colour	Green
title	LOGIC

Limits the value that can be placed in a column

COALESCE

Status

subtle	true
colour	Green
title	LOGIC

Returns the first non-null value in a list (see an example)

...

CONCAT

Status

subtle	true
colour	Green
title	MERGING

...

Concatenates values (see an example)

CONCAT_VIEW

...

Status

title
subtle	true
colour	Green

...

MERGING

Concatenates values, preserving their original HTML format (see an example)

CONCAT_VIEW_AGGR

Status

title
subtle	true
colour	Green

...

MERGING

...

Status

title
subtle	true
colour	Green

...

AGGREGATION

Concatenates

...

and aggregates values, preserving their original HTML format (see an example)

CORRESPONDING

Status

subtle	true
colour	Green
title	MERGING

...

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

COUNT

...

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	AGGREGATION

...

Returns the number of rows that matches a specified criteria (see an example)

CROSS

...

Status

title
subtle	true
colour	Green

...

MERGING

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

CUBE

Status

subtle	true
colour	Green
title	MATH

Status

subtle

true
colour	Green
title	AGGREGATION

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

CURRENT_TIMESTAMP

Status

subtle	true
colour	Green
title	VARIABLES

Status

subtle	true
colour	Green
title	TIME

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

DATE

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	CONVERTING

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

...

DATE_ADD

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	TIME

Adds time values (as intervals) to a date value

DATE_

...

SUB

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	TIME

...

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

DATEADD

...

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	TIME

...

Adds time values (as intervals) to a date value (see an example)

DATEDIFF

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	TIME

Returns the difference between two dates (see an example)

DAY

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	TIME

Status

subtle	true
colour	Green
title	CONVERTING

Returns the day of the month for a date

...

DAYOFWEEK

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

title
subtle	true
colour	Green

...

TIME

...

Returns the weekday index for a

...

date

...

DEFAULT

Status

title
subtle	true
colour	Green

...

DATA_MANIPULATING

Status

subtle	true
colour	Green
title	LOGIC

Provides a default value for a column

DESC

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	ORDERING

Descending sorting order

DISTINCT

Status

subtle	true
colour	Green
title	LOGIC

Status

subtle	true
colour	Green
title	AGGREGATION

Selects only distinct (different) values (see an example)

ELSE

Status

subtle	true
colour	Green
title	LOGIC

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

END

Status

subtle	true
colour	Green
title	LOGIC

Completes code block for WHILE and IF statements

...

ESCAPEMARKUP

Status

title
subtle	true
colour	Green

...

STRING_OPERATORS

Escapes special characters for wiki markup (see an example)

ESC ESCAPE APE

Status

title
subtle	true
colour	Green

...

LOGIC

...

Status

subtle	true
colour	Green
title	SEARCHING

...

Used with LIKE, escapes characters

EXCEPT

Status

subtle	true
colour	Green
title	MERGING

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

EXISTS

Status

subtle	true
colour	Green
title	LOGIC

Tests for the existence of any record in a subquery (see an example)

EXP

Status

subtle	true
colour	Green
title	MATH

Returns e raised to the power of the specified number

...

FALSE

Status

title
subtle	true
colour	Green

...

LOGIC

Logical false value

FIND_VIEW

...

Status

title
subtle	true
colour	Green

...

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

...

DATA_MANIPULATING

FIND_VIEW(value, selector, flag) - extracts values from specified HTML tags (see an example):

value – the table column containing HTML content
selector – the HTML tag to search for (e.g., "h3")
flag (optional) – set to false/0 (default) to return the first match, or true/1 to return all matching values

FIRST

Status

subtle	true

title
colour	Green

...

AGGREGATION

Returns the first value in the group

FLOOR

Status

subtle	true
colour	Green
title	MATH

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

FORMATDATE

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	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)

FORMATMARKDOWN

Status

subtle	true
colour	Green
title	FORMATTING

Applies Markdown syntax to table cells (see an example)

FORMATNUMBER

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	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)

FORMATTIME

...

Status

title
subtle	true
colour	Green

...

DATA_MANIPULATING

...

Status

title
subtle	true
colour	Green

...

TIME

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

FORMATWIKI

...

Status

subtle	true
colour	Green
title	FORMATTING

...

Applies Confluence Wiki Markup to table cells (see an example)

FORMATWORKLOG

...

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	TIME

Converts

...

worklogs to the specified in the Table Transformer macro worklog settings

...

: FORMATWORKLOG (calculated_

...

worklog) (see an example)

FROM

Status

subtle	true
colour	Green
title	KEYWORD

Specifies which table to select or delete data from

FULL

...

Status

title
subtle	true
colour	Green

...

MERGING

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

GETDATE

...

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	TIME

...

Returns the current system date and time

GREATEST

...

Status

title
subtle	true
colour	Green

...

MATH

Status

title
subtle	true
colour	Green

...

AGGREGATION

Returns the greatest value of the list of arguments

GROUP

...

Status

title
subtle	true
colour	Green

...

AGGREGATION

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

GROUP_CONCAT

Status

subtle	true
colour	Green
title	AGGREGATION

...

Status

colour
subtle	true

...

	Green
title	MERGING

Returns a string with concatenated non-NULL value from a group. It is used to concatenate and aggregate values from multiple rows within a specific column into a single string.

Please note that the default separator (comma) is used and it can't be changed

GROUPING

Status

title
subtle	true
colour	Green

...

AGGREGATION

Status

title
subtle	true
colour	Green

...

LOGIC

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

HAVING

Status

title
subtle	true
colour	Green

...

AGGREGATION

...

Status

title
subtle	true
colour	Green

...

LOGIC

Used instead of WHERE with aggregate functions

HOUR

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	TIME

...

Returns the hour part of a datetime

HTML_DECODE

Status

subtle	true
colour	Green
title	STRING_OPERATORS

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

IF

Status

subtle	true
colour	Green
title	LOGIC

Conditional statement

IFNULL

Status

subtle	true
colour	Green
title	LOGIC

Returns a specified value if the expression is NULL

IIF

Status

subtle	true
colour	Green
title	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

IN

Status

subtle	true
colour	Green
title	LOGIC

Allows you

...

to specify multiple values in a WHERE clause

IN_LIST

Status

subtle	true
colour	Green
title	LOGIC

Status

subtle	true
colour	Green
title	STRING_OPERATORS

Checks whether the first argument exists within the second (an array or list): IN_LIST(value, list). See an example

INNER

Status

subtle	true
colour	Green
title	MERGING

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

INSERT

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Inserts new records into a table (see an example)

INSTR

Status

subtle	true
colour	Green
title	STRING_OPERATORS

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

INTERSECT

Status

subtle	true
colour	Green
title	MERGING

Intersects two SELECT results

INTERVAL

Status

subtle	true
colour	Green
title	TIME

Defines a date/time interval

IS

Status

subtle	true
colour	Green
title	LOGIC

ISNULL

Status

subtle	true
colour	Green
title	LOGIC

Returns TRUE or FALSE depending on whether an expression is NULL

JOIN

Status

subtle	true
colour	Green
title	MERGING

Joins tables (see an example)

LAST

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Returns the last value of the selected column

LCASE

Status

subtle	true
colour	Green
title	STRING_OPERATORS

Converts a string to lower-case

LEAST

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	AGGREGATION

Returns the smallest value of the list of arguments

LEFT

Status

subtle	true
colour	Green
title	MERGING

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

LENGTH

Status

subtle	true
colour	Green
title	STRING_OPERATORS

Returns the length of a string (in bytes)

LIKE

Status

subtle	true
colour	Green
title	LOGIC

Status

subtle	true
colour	Green
title	SEARCHING

Searches for a specified pattern

...

in a column

LIMIT

Status

subtle	true
colour	Green
title	SEARCHING

Limit the number of records from the result set

LOG

Status

subtle	true
colour	Green
title	MATH

Returns the natural logarithm

MATCH_REGEXP

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

...

Status

title
subtle	true
colour	Green

...

LOGIC

Status

title
subtle	true
colour	Green

...

	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)

MAX

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	AGGREGATION

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

MEDIAN

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	AGGREGATION

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

MID

Status

subtle	true
colour	Green
title	STRING_OPERATORS

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

MIN

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	AGGREGATION

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

MINUTE

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	TIME

Returns the minute part of a datetime value

MONTH

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	TIME

Returns the month part of a date

MULT

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	AGGREGATION

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

NATURAL

Status

subtle	true
colour	Green
title	MERGING

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

NOT

Status

subtle	true
colour	Green
title	LOGIC

Includes rows where a condition is not TRUE

NOW

Status

subtle	true
colour	Green
title	VARIABLES

Returns current date and time

NULL

Status

subtle	true
colour	Green
title	LOGIC

A field with no value

NULLIF

Status

subtle	true
colour	Green
title	LOGIC

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

OF

Status

subtle	true
colour	Green
title	LOGIC

Allows you to specify multiple values in a WHERE clause

OFFSET

Status

subtle	true
colour	Green
title	SEARCHING

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

ON

Status

subtle	true
colour	Green
title	MERGING

OR

Status

subtle	true
colour	Green
title	LOGIC

Includes rows where either condition is TRUE

ORDER

Status

subtle	true
colour	Green
title	ORDERING

Sorts the result set in ascending or descending order

OUT

Status

subtle	true
colour	Green
title	LOGIC

Opposite to IN

OUTER

Status

subtle	true
colour	Green
title	MERGING

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

PERCENT

Status

subtle	true
colour	Green
title	SEARCHING

Indicates that the query returns N percentage of rows

PIVOT

Status

subtle	true
colour	Green
title	AGGREGATION

Converts data from row level to column level (see an example)

POWER

Status

subtle	true
colour	Green
title	MATH

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

QUART

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	AGGREGATION

Returns the first quartile of a group

QUART2

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	AGGREGATION

Returns the second quartile of a group

QUART3

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	AGGREGATION

Returns the third quartile of a group

RANDOM

Status

subtle	true
colour	Green
title	VARIABLES

REGEXP_LIKE

Status

title
subtle	true
colour	Green

...

	LOGIC

Status

subtle	true
colour	Green
title	STRING_OPERATORS

Status

subtle	true
colour	Green
title	SEARCHING

Returns rows that match a regular expression pattern

...

REGEXP_

...

REPLACE

Status

subtle	true
colour	Green
title	LOGIC

...

Status

subtle	true
colour	Green
title	STRING_OPERATORS

...

Status

subtle	true
colour	Green
title	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)

REPLACE

Status

subtle	true
colour	Green
title	STRING_OPERATORS

Replaces all occurrences of a substring within a string

...

with a new substring

REPLACE_VIEW

Status

subtle	true
colour	Green
title	STRING_OPERATORS

Replaces all occurrences of a substring within a string with a new substring and preserves the original HTML format (see an example)

RIGHT

Status

subtle	true
colour	Green
title	MERGING

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

ROLLUP

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	AGGREGATION

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

ROUND

Status

subtle	true
colour	Green
title	MATH

Rounds a number to a specified number of decimal places

ROWNUM

...

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

...

Adds row numbering to a table (see an example)

SEARCH

...

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	TIME

...

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

...

Searches for a specified pattern (see an example)

SECOND

...

Status

title
subtle	true
colour	Green

...

DATA_MANIPULATING

...

Status

title
subtle	true
colour	Green

...

TIME

...

Return the

...

seconds part of a

...

datetime value

...

SELECT

Status

title
subtle	true
colour	Green

...

	DATA_MANIPULATING

Selects data from a database

SOME

Status

title
subtle	true
colour	Green

...

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

SPLIT

...

Status

title
subtle	true
colour	Green

...

DATA_MANIPULATING

...

Status

title
subtle	true
colour	Green

...

STRING_OPERATORS

Splits a string into an array of substrings using a separator (see an example)

...

SPLIT_VIEW

Status

title
subtle	true
colour	Green

...

DATA_MANIPULATING

...

Status

title
subtle	true
colour	Green

...

STRING_OPERATORS

Splits a string into an array of substrings using a separator and preserves the original HTML format (see an example)

SQRT

...

Status

title
subtle	true
colour	Green

...

MATH

Returns the square root of a number

STDDEV

Status

subtle	true
colour	Green
title	MATH

...

Status

title
subtle	true
colour	Green

...

AGGREGATION

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

STDEV

...

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	AGGREGATION

...

Returns the

...

sample standard deviation of expression (see an example)

...

SUBDATE

Status

subtle	true
colour	Green
title	MATH

...

Status

title
subtle	true
colour	Green

...

TIME

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

SUBSTR

...

Status

title
subtle	true
colour	Green

...

STRING_OPERATORS

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

SUBSTRING

...

Status

title
subtle	true
colour	Green

...

STRING_OPERATORS

Allows to extract a substring from a string

SUBSTRING_VIEW

...

Status

title
subtle	true
colour	Green

...

STRING_OPERATORS

Allows to extract a substring from a string and preserves the original HTML format (see an example)

SUM

...

Status

title
subtle	true
colour	Green

...

MATH

...

Status

title
subtle	true
colour	Green

...

AGGREGATION

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

TEXT

...

Status

subtle	true
colour	Green
title	STRING_OPERATORS

...

Formats the source data as string and returns it without additional formatting (see an example)

THEN

...

Status

title
subtle	true
colour	Green

...

LOGIC

...

A result expression in a search condition

TIME

...

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

...

Status

subtle	true
colour	Green
title	TIME

...

	Extracts the time part from a given time/datetime
TIMESTAMP

...

Status

title
subtle	true
colour	Green

...

CONVERTING

Returns a datetime value based on a date or datetime value

TIMESTAMPDIFF

...

Status

subtle	true
colour	Green
title	MATH

Status

title
subtle	true
colour	Green

...

TIME

Returns the

...

difference between two dates

TO

...

Status

subtle	true
colour	Green
title	AGGREGATION

...

Status

title
subtle	true
colour	Green

...

KEYWORD

TO_LIST

Status

subtle	true
colour	Green

...


title	DATA_MANIPULATING

...

Status

title
subtle	true
colour	Green

...

STRING_OPERATORS

...

Splits the first argument into a list using the second argument as the delimiter (defaults to a comma), extra spaces are automatically trimmed: TO_LIST(value, optional_delimiter). See an example

TRIM

...

Status

title
subtle	true
colour	Green

...

	STRING_OPERATORS

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

TRUE

...

Status

title
subtle	true
colour	Green

...

LOGIC

...

Logical true value

UCASE

...

Status

title
subtle	true
colour	Green

...

STRING_OPERATORS

Converts a string to upper-case

UNION

...

Status

title
subtle	true
colour	Green

...

	MERGING

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

UNPIVOT

Status

title
subtle	true
colour	Green

...

AGGREGATION

Converts data from column level to row level, ungroups data (see an example)

UPDATE

...

Status

title
subtle	true
colour	Green

...

DATA_MANIPULATING

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

UPPER

...

Status

title
subtle	true
colour	Green

...

STRING_OPERATORS

Converts a string to upper-case

USING

...

Status

title
subtle	true
colour	Green

...

LOGIC

Matches only one column when more than one column matches

VAR

...

Status

title
subtle	true
colour	Green

...

MATH

...

Status

title
subtle	true
colour	Green

...

AGGREGATION

Returns the statistical variance of all values in the specified expression (see an example)

VAR_POP

Status

colour
subtle	true

...

	Green
title	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

VAR_SAMP

...

Status

title
subtle	true
colour	Green

...

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

VARCHAR

...

Status

title
subtle	true
colour	Green

...

KEYWORD

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

VARP

Status

title
subtle	true
colour	Green

...

	AGGREGATION

Returns the Variance for the population of the total number of records present in the specified column (see an example)

WEEKDAY

Status

title
subtle	true
colour	Green

...

DATA_MANIPULATING

Status

title
subtle	true
colour	Green

...

TIME

Returns the weekday number for a given date

WHEN

Status

subtle	true
colour	Green
title	LOGIC

...

A condition expression in a search condition

WHERE

...

Status

title
subtle	true
colour	Green

...

LOGIC

Status

title
subtle	true
colour	Green

...

SEARCHING

...

Returns the weekday index for a date

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

WHILE

Status

subtle	true
colour	Green
title	LOOP

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

YEAR

...

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	TIME

Returns

...

an integer value which represents the year of the specified date

Conversion types

::Date
::string
::number

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

JavaScript methods

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

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

Find an example here.

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

Code Block
'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:

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

Info

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

Code Block
'Column with numbers'->toFixed(1)

Example

The source table:
Image Modified

The result:
Image Modified

SQL query:

Code Block

language	sql

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*

Page tree

Versions Compared

Old Version 1

New Version 71

Key

Autocompletion

Custom SQL query editor powered by AI

General SQL syntax

General SQL syntax

Functions and keywords

Conversion types

JavaScript methods

Example