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 its part

...

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'

...

: 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

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
id	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

subtle	true
colour	Green
title	MERGING

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

CONCAT_VIEW_AGGR

Status

subtle	true
colour	Green
title	MERGING

Status

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

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

subtle	true
colour	Green
title	TIME

Returns the weekday index for a date

DEFAULT

Status

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

ESC ESCAPE APE

Status

subtle	true
colour	Green
title	LOGIC

Status

subtle	true
colour	Green
title	SEARCHING

...

Used with LIKE, escapes characters

ESCAPEMARKUP

Status

subtle	true
colour	Green
title	STRING_OPERATORS

Escapes special characters for wiki markup (see an example)

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

subtle	true
colour	Green
title	LOGIC

Logical false value

FIRST

Status

subtle	true
colour	Green
title	AGGREGATION

Returns the first value in the group

FLOOR

Status

...

subtle	true
colour	Green
title	MATH

...

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

subtle	true
colour	Green
title	DATA_MANIPULATING

Status

subtle	true
colour	Green
title	TIME

Converts

...

time to the specified in the Table Transformer macro settings

...

time formatto 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

title
subtle	true
colour	Green

...

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

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

GROUPING

...

Status

title
subtle	true
colour	Green

...

LOGIC

Status

subtle	true
colour	Green
title	AGGREGATION

...

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

...

Status

title
subtle	true
colour	Green

...

LOGIC

Status

title
subtle	true
colour	Green

...

AGGREGATION

Used instead of WHERE with aggregate functions

HOUR

...

Status

title
subtle	true
colour	Green

...

DATA_MANIPULATING

Status

title
subtle	true
colour	Green

...

TIME

...

Returns the hour part of a datetime

HTML_DECODE

...

Status

title
subtle	true
colour	Green

...

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

title
subtle	true
colour	Green

...

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

title
subtle	true
colour	Green

...

LOGIC

Allows you to specify multiple values in a WHERE clause

INNER

...

Status

title
subtle	true
colour	Green

...

MERGING

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

INSERT

...

Status

title
subtle	true
colour	Green

...

DATA_MANIPULATING

Inserts new records into a table (see an example)

INSTR

...

Status

title
subtle	true
colour	Green

...

STRING_OPERATORS

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

INTERSECT

...

Status

title
subtle	true
colour	Green

...

MERGING

Intersects two SELECT results

INTERVAL

...

Status

title
subtle	true
colour	Green

...

TIME

Defines a date/time interval

IS

...

Status

subtle	true
colour	Green
title	LOGIC

...

Allows you to specify multiple values in a WHERE clause

...

ISNULL

Status

title
subtle	true
colour	Green

...

LOGIC

Returns TRUE or FALSE depending on whether an expression is NULL

JOIN

...

Status

title
subtle	true
colour	Green

...

MERGING

Joins tables (see an example)

...

LAST

Status

title
subtle	true
colour	Green

...

DATA_

...

MANIPULATING

Returns the

...

last value of the

...

selected column

LCASE

...

Status

title
subtle	true
colour	Green

...

STRING_OPERATORS

Converts a string to lower-case

LEAST

...

Status

subtle	true
colour	Green
title	TIME

...

Status

title
subtle	true
colour	Green

...

MATH

...

Status

title
subtle	true
colour	Green

...

Returns TRUE or FALSE depending on whether an expression is NULL

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

title
subtle	true
colour	Green

...

STRING_

...

OPERATORS

Returns the

...

length of a string (in bytes)

LIKE

Status

subtle	true
colour	Green
title	LOGIC

...

Status

title
subtle	true
colour	Green

...

SEARCHING

Searches for a specified pattern in a column

LIMIT

...

Status

title
subtle	true
colour	Green

...

	SEARCHING

Limit the number of records from the result set

LOG

Status

title
subtle	true
colour	Green

...

MATH

...

Returns the

...

natural logarithm

MATCH_REGEXP

...

Status

title
subtle	true
colour	Green

...

DATA_MANIPULATING

Status

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

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 largest value in that column

MEDIAN

Status

subtle	true
colour	Green
title	MATH

...

Status

title
subtle	true
colour	Green

...

AGGREGATION

...

Returns the

...

median value of a group (see an example)

MID

...

Status

title
subtle	true
colour	Green

...

	STRING_OPERATORS

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

MIN

...

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 smallest value in that column

MINUTE

...

Status

title
subtle	true
colour	Green

...

DATA_MANIPULATING

Status

title
subtle	true
colour	Green

...

TIME

...

Returns the

...

minute part of a

...

datetime value

MONTH

...

Status

title
subtle	true
colour	Green

...

DATA_MANIPULATING

Status

title
subtle	true
colour	Green

...

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

title
subtle	true
colour	Green

...

MERGING

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

NOT

...

Status

title
subtle	true
colour	Green

...

	LOGIC

Includes rows where a condition is not TRUE

NOW

Status

title
subtle	true
colour	Green

...

VARIABLES

Returns current date and time

NULL

...

Status

title
subtle	true
colour	Green

...

LOGIC

A field with no value

NULLIF

...

Status

title
subtle	true
colour	Green

...

LOGIC

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

OF

...

Status

title
subtle	true
colour	Green

...

LOGIC

Allows you to specify multiple values in a WHERE clause

OFFSET

...

Status

title
subtle	true
colour	Green

...

SEARCHING

...

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

ON

...

Status

title
subtle	true
colour	Green

...

MERGING

...

OR

Status

title
subtle	true
colour	Green

...

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

LOGIC

Includes rows where either condition is TRUE

ORDER

...

Status

title
subtle	true
colour	Green

...

ORDERING

Sorts the result set in ascending or descending order

OUT

...

Status

subtle	true
colour	Green
title	LOGIC

...

Opposite to IN

OUTER

...

Status

title
subtle	true
colour	Green

...

MERGING

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

PERCENT

...

Status

title
subtle	true
colour	Green

...

SEARCHING

Indicates that the query returns N percentage of rows

PIVOT

...

Status

title
subtle	true
colour	Green

...

AGGREGATION

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

POWER

...

Status

title
subtle	true
colour	Green

...

MATH

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

QUART

Status

subtle	true
colour	Green
title	MATH

...

Status

title
subtle	true
colour	Green

...

AGGREGATION

...

Returns the first

...

quartile of a group

QUART2

...

Status

title
subtle	true
colour	Green

...

MATH

...

Status

title
subtle	true
colour	Green

...

AGGREGATION

Returns the second quartile of a group

QUART3

...

Status

title
subtle	true
colour	Green

...

MATH

Status

title
subtle	true
colour	Green

...

AGGREGATION

Returns the third quartile of a group

RANDOM

...

Status

title
subtle	true
colour	Green

...

VARIABLES

REGEXP_LIKE

...

Status

title
subtle	true
colour	Green

...

LOGIC

...

Status

title
subtle	true
colour	Green

...

Converts data from row level to column level

STRING_OPERATORS

...

Status

title
subtle	true
colour	Green

...

SEARCHING

Returns

...

rows that match a regular expression pattern

REGEXP_REPLACE

...

Status

title
subtle	true
colour	Green

...

LOGIC

...

Status

title
subtle	true
colour	Green

...

STRING_OPERATORS

Status

title
subtle	true
colour	Green

...

	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

title
subtle	true
colour	Green

...

STRING_OPERATORS

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

REPLACE_VIEW

...

Status

title
subtle	true
colour	Green

...

Status

subtle	true
colour	Green
title	AGGREGATION

...

Status

subtle	true
colour	Green
title	VARIABLES

...

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

title
subtle	true
colour	Green

...

	MERGING

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

ROLLUP

Status

title
subtle	true
colour	Green

...

MATH

Status

title
subtle	true
colour	Green

...

AGGREGATION

...

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

ROUND

...

Status

title
subtle	true
colour	Green

...

MATH

Rounds a number to a specified number of decimal places

ROWNUM

...

Status

title
subtle	true
colour	Green

...

DATA_

...

MANIPULATING

Adds row numbering to a table (see an example)

SEARCH

...

Status

title
subtle	true
colour	Green

...

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

subtle	true
colour	Green
title	MATH

...

Status

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

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

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

subtle	true
colour	Green
title	MATH

Returns the square root of a number

STDDEV

Status

subtle	true
colour	Green
title	MATH

Status

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

subtle	true
colour	Green
title	TIME

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

SUBSTR

Status

subtle	true
colour	Green
title	STRING_OPERATORS

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

SUBSTRING

Status

subtle	true
colour	Green
title	STRING_OPERATORS

Allows to extract a substring from a string

SUBSTRING_VIEW

Status

subtle	true
colour	Green
title	STRING_OPERATORS

Allows to extract a substring from a string

...

and preserves the original HTML format (see an example)

SUM

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

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

subtle	true
colour	Green
title	CONVERTING

Returns a datetime value based on a date or datetime value

TIMESTAMPDIFF

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	TIME

Returns the difference between two dates

TO

Status

subtle	true
colour	Green
title	KEYWORD

TRIM

Status

subtle	true
colour	Green
title	STRING_OPERATORS

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

TRUE

Status

subtle	true
colour	Green
title	LOGIC

Logical true value

UCASE

Status

subtle	true
colour	Green
title	STRING_OPERATORS

Converts a string to upper-case

UNION

Status

subtle	true
colour	Green
title	MERGING

Combines the result set of two or more SELECT statements (

...

only distinct values)

UNPIVOT

Status

subtle	true
colour	Green
title	AGGREGATION

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

UPDATE

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

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

UPPER

Status

subtle	true
colour	Green
title	STRING_OPERATORS

Converts a string to upper-case

USING

Status

subtle	true
colour	Green
title	LOGIC

Matches only one column when more than one column matches

VAR

Status

subtle	true
colour	Green
title	MATH

Status

subtle	true
colour	Green
title	AGGREGATION

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

VAR_POP

Status

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

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

subtle	true
colour	Green
title	KEYWORD

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

VARP

Status

subtle	true
colour	Green
title	AGGREGATION

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

WEEKDAY

Status

subtle	true
colour	Green
title	DATA_MANIPULATING

...

Status

subtle	true
colour	Green
title	TIME

...

Status

subtle	true
colour	Green
title	LOGIC

...

Status

title
subtle	true
colour	Green

...

TIME

...

Returns the weekday number for a given date

WHEN

...

Status

title
subtle	true
colour	Green

...

LOGIC

A condition expression in a search condition

WHERE

...

Status

title
subtle	true
colour	Green

...

LOGIC

Status

title
subtle	true
colour	Green

...

Status

subtle	true
colour	Green
title	MATH

SEARCHING

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

WHILE

Status

title
subtle	true
colour	Green

...

LOOP

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

YEAR

...

Status

title
subtle	true
colour	Green

...

DATA_

...

MANIPULATING

Status

title
subtle	true
colour	Green

...

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 2

New Version 3

Key

Autocompletion

Custom SQL query editor powered by AI

General SQL syntax

General SQL syntax

Functions and keywords

Conversion types

JavaScript methods

Example