Page tree

Example

Let's build the email address from the first and last name:

  • Autocompletion: Start typing "Lower..." and Play SQL will autosuggest function names. We've used the names of Excel functions as keywords so you find your way easily.
  • Click on a cell to include the reference to this cell
    • Cells of the same row can be referenced using the column name: 
    • Cells of another row are referenced using ":" and the row ID: 
  • Operators such as + - / *, AND, OR, XOR, NOT can be used as usual, for strings, booleans and numbers,
  • Strings are quoted using single quotes: 'string'. Quotes are escaped using \: 'O\'Farrel'.
  • The formula can be validated with , or cancelled using .
  • It is possible to apply the formula to all visible rows by clicking on the column header and pressing Ctrl+V. 

Functions for numbers

Numbers 
VALUE(text)Converts text to a number
ABS(value)Absolute value
ROUND(value[, decimals])Rounds the number to the nearest integer
TRUNC(value[, decimals])Truncates to the lower integer
AVG(column:*)Average of all numbers in the column. Note that an empty cell will not count, whereas a cell with 0 will weigh down. See the section "Cell references" to learn how to sum with a criteria.
MIN(...), MAX(...), SUM(...)Same as AVG for the minimum, maximum and sum.
COUNT(column:*)Counts the number of non-null cells. Note null cells are excluded but empty cells are not. To count all rows in a table, use the ID column: COUNT(ID:*)

Text functions

Text 
FIND(subtext, text)Find one text within another. Example: FIND('needle', 'haystack with one needle') returns 19. Returns 0 if not found.
CONTAINS(subtext, text)Returns true or false whether text contains subtext. Example: CONTAINS('needle', 'haystack with one needle') returns TRUE.
STARTS_WITH(subtext, text)Returns true or false whether text starts with subtext. Example: STARTS_WITH('needle', 'haystack with one needle') returns FALSE.
SUBSTR(text, startAt, [length])Returns a substring of text. Example: SUBSTR('team', 1, 3) returns 'tea'.
REPLACE(text, search, replace)Replaces a part of the string: REPLACE('haystack with one needle', 'one', 'no') returns 'haystack with no needle'.
REGEX(text, search[, replace])Same as REPLACE, but with regular expressions. Parenthesis capture groups which can be reused using \1, \2, etc.
Example:REGEX('haystack with 1 needle', '([0-9]*)', '_\1_') returns 'haystack with _1_ needle'.
LOWER(text), UPPER(text), CAPITALIZE(text)Returns the lowercase, uppercase or capitalized text. Example: CAPITALIZE('The origin of love') returns 'The Origin Of Love'.
LENGTH(text)Returns the length of the text. Returns NULL if there's no text or 0 if the text is empty.

General functions

FunctionResult
+, *, /, -Addition for numbers, concatenation for strings

IF(condition, result_if_true, result_if_false)

Conditionals
NVL(maybe_null, value_if_null)Returns the first value if not null, otherwise the second value.
ISNULL(value)Returns true if 'value' is NULL.
ISEMPTY(value)Returns true if 'value' is NULL or empty ('').
LOOKUP(value, lookupColumn, resultColumn)See "range references" below.
SQL(...)Executes an arbitrary SQL function. Example: SQL('SELECT NOW()') returns a date.
(warning) That's a workaround. If you need to use it, please tell us!

Cell references, ranges and lookups

Cell reference

 
column_nameIn the same row, the value of the column 'column_name'. Case-sensitive.
column_name:28In the row with ID=28, the value of the column 'column_name'
Range referenceNote: Ranges can sometimes be used as a single value.
column_name:*All the values from the column 'column_name'. Returns a range.
column_name:[condition]The value of all cells where condition is true. Returns a range.
Example: animal.name:[horns > 0] returns unicorns and cows.
table.column_name:*Same as above, for the given table. Returns a range.
FIRST(range)Converts a range to a single cell by returning the first value only.
Lookups 
LOOKUP(value, lookupColumn, resultColumn)Search lookupColumn for value. If found, returns resultColumn.
Example: LOOKUP(name, subscriptions.name, subscriptions.contribution) will return the contribution of the subscriber with name "name".
Returns a range, which may be used as a single value.
Note: This syntax is exactly identical to the following syntax. 
resultColumn:[lookupColumn=value]

Returns resultColumn where the condition is true.
In case of ambiguity with the column names in the condition, the first part refers to the local table.
Example: employee.name:[ID=ID] is the same as employee.name:[employee.ID=localtable.ID].

 

Conversion functions

Function 
VALUE(text)Returns the number value of text
AS_BOOLEAN(text)Return the boolean value of text. Example: AS_BOOLEAN('y') returns TRUE.

 

More examples

FunctionReturns
=firstcolumn:2 + secondcolumn:3The concatenation of the texts in the first column, row 2 and the second column, row 3.
=SUM()The sum of all numbers in the first column.
=SUM(firstcolumn:[ID = 3 or ID = 4] )The sum of all numbers in the first column where the ID is 3 or 4.

=

The value of the first column, row 7.
  • No labels