All Functions

Modulo (%)

Syntax:number_1 % number_2

Returns the remainder from dividing the first number number_1 by the second number number_2.

Multiplication (*)

Syntax:value_1 * value_2

If both arguments are numbers, it returns the result by multiplying value_1 by value_2.

If one of the arguments is String and the other is Number (whole), it returns the string repeated the specified number of times.

Addition and concatenation (+)

Syntax:value_1 + value_2

Behaves differently depending on the argument types. Possible options are listed in the table:

Type of value_1 Type of value_2 Return value
Number Number The sum of the numbers value_1 and value_2
Date Number The date that is value_2 days greater than value_1 (rounded down to an integer number of days)
Datetime Number The date with time, value_2 days greater than value_1. If value_2 contains a fractional part, it is converted hours (1/24), minutes (1/1440), and seconds (1/86400)
String String The merging (concatenation) of strings value_1 and value_2

Changing the order of arguments does not affect the result.

Subtraction (-)

Syntax:value_1 - value_2

Behaves differently depending on the argument types. Possible options are listed in the table:

Type of value_1 Type of value_2 Return value
Number Number The difference between the numbers value_1 and value_2
Date Number The date that is value_2 days smaller than value_1 (rounded down to an integer number of days)
Datetime Number The date with time, value_2 days smaller than value_1. If value_2 contains a fractional part, it is converted to hours (1/24), minutes (1/1440), and seconds (1/86400)
Date Date The difference between two dates in days
Datetime Datetime The difference between two dates in days: the integer part — the number of whole days, the fractional part — the number of hours, minutes and seconds expressed as a fraction of the whole day (1 hour is '1/24')

Division (/)

Syntax:number_1 / number_2

Divides the number number_1 by the number number_2.

Comparison

Syntax:
value_1 = value_2
or
value_1 != value_2
or
value_1 < value_2
or
value_1 <= value_2
or
value_1 > value_2
or
value_1 => value_2

Compares the value value_1 with the value value_2.

Power (^)

Syntax:base ^ power

Raises base to the power of power.

CASE

Syntax:
CASE expression WHEN value_1 THEN result_1 [ WHEN value_2 THEN result_2 ... ] ELSE default_result END
or
CASE( expression, value_1, result_1, [ value_2, result_2, ... ] default_result )

Compares expression to value_1, value_2, ... consecutively and returns the corresponding result for the first match. If no match is found, it returns default_result.

IF

Syntax:
IF condition_1 THEN result_1 [ ELSEIF condition_2 THEN result_2 ... ] ELSE default_result END
or
IF( condition_1, result_1, [ condition_2, result_2, ... ] default_result )

Checks conditional expressions condition_1, result_1, ... and returns the matching result for the first condition found to be TRUE. IF all conditional expressions are FALSE, it returns default_result.

ABS

Syntax:ABS( number )

Returns the absolute value of number.

ACOS

Syntax:ACOS( number )

Returns the arccosine of number in radians.

AND

Syntax:value_1 AND value_2

Performs a Boolean join of two expressions with the AND condition.

ASCII

Syntax:ASCII( string )

Returns the numeric representation of the first character of the string.

ASIN

Syntax:ASIN( number )

Returns the arcsine of number in radians.

ATAN

Syntax:ATAN( number )

Returns the arctangent of number in radians.

ATAN2

Syntax:ATAN2( x, y )

Returns the arctangent in radians for the specified coordinates x and y.

AVG

Syntax:AVG( value )

Returns the average of all values. Applicable to numeric data types as well as Date | Datetime.

AVG_IF

Syntax:AVG_IF( expression, condition )

Returns the average of all values that meet the condition condition. If the values don't exist, it returns NULL. Applicable to numeric data types only.

BETWEEN

Syntax:value [ NOT ] BETWEEEN low AND high

Returns TRUE if value is in the range from low to high.

The option value NOT BETWEEEN low AND high returns the opposite value.

BOOL

Syntax:BOOL( expression )

Converts the expression expression to Boolean type according to the following rules:

Type FALSE TRUE
Number 0, 0.0 All others
String Empty string ("") All others
Boolean FALSE TRUE
Date | Datetime - TRUE

CEILING

Syntax:CEILING( number )

Rounds the value up to the nearest integer.

CHAR

Syntax:CHAR( string )

Converts the numeric representation of an ASCII character to a value.

CONCAT

Syntax:CONCAT( arg_1, arg_2, arg_3 [ , ... ] )

Merges any number of strings. When non-string types are used, they're converted to strings and then merged.

CONTAINS

Syntax:CONTAINS( string, substring )

Returns TRUE if string contains substring. For case-insensitive searches, see ICONTAINS.

COS

Syntax:COS( number )

Returns the cosine of number in radians.

COT

Syntax:COT( number )

Returns the cotangent of number in radians.

COUNT

Syntax:COUNT( [ value ] )

Returns the number of items in the group.

COUNT_IF

Syntax:COUNT_IF( condition )

Returns the number of items in the group meeting the condition condition.

COUNTD

Syntax:COUNTD( value )

Returns the number of unique values in the group.

See also COUNTD_APPROX.

COUNTD_APPROX

Syntax:COUNTD_APPROX( value )

Returns the approximate number of unique values in the group. Faster than COUNTD, but doesn't guarantee accuracy.

COUNTD_IF

Syntax:COUNTD_IF( expression, condition )

Returns the number of unique values in the group that meet the condition condition.

See also COUNTD_APPROX.

DATE

Syntax:DATE( expression [ , timezone ] )

Converts the expression expression to date format.

The date must be in the format YYYY-MM-DD.

If expression is a number, then the timezone option can be used to convert the date to the specified time zone.

DATE_PARSE

Syntax:DATE_PARSE( value )

Converts the value expression to date format. Unlike DATE, it supports multiple formats.

DATEADD

Syntax:DATEADD( datetime, unit, number )

Returns the date obtained by adding unit in the amount of number to the specified date datetime.

The number argument is an integer. It can be negative. The unit argument takes the following values:

  • "year"
  • "month"
  • "day"
  • "hour"
  • "minute"
  • "second"

DATEPART

Syntax:DATEPART( datetime, unit [ , firstday ] )

Returns a part of the date as an integer.

Depending on the argument, unit returns the following values:

  • "year" — The year number (see YEAR).
  • "month" — The number of the month in the year (see MONTH).
  • "week" — The number of the week in the year according to ISO 8601 (see WEEK).
  • "dayofweek", "dow" — The number of the day of the week (see DAYOFWEEK). "day" — The number of the day in the month (see DAY).
  • "hour" — The number of the hour in the day (see HOUR).
  • "minute" — The number of the minute in the hour (see MINUTE).
  • "second" — The number of the second in the minute (see SECOND).

If you select "dayofweek", you can use the additional parameter firstday to specify the first day of the week (Monday by default). Learn more about this parameter in the DAYOFWEEK function description

DATETIME

Syntax:DATETIME( expression [ , timezone ] )

Converts the expression expression to date and time format. When converting Date to DateTime, the time is set to '00:00:00'. The date must be in the format YYYY-MM-DDThh:mm:ss or YYYY-MM-DD hh:mm:ss.

If expression is a number, then the date and time can be converted to the specified time zone when the timezone option is available.

DATETIME_PARSE

Syntax:DATETIME_PARSE( value )

Converts the value expression to date and time format. Unlike DATETIME, it supports multiple formats.

DATETRUNC

Syntax:DATETRUNC( datetime, unit [ , number ] )

Rounds datetime down to the given unit. If optional number is given, then the value is rounded down to a number multiple of unit (omitting number is the same as number = 1).

Supported units:

  • "second"
  • "minute"
  • "hour"
  • "day" (acts as the day of the year if number is specified)
  • "week"
  • "month"
  • "year"

DAY

Syntax:DAY( datetime )

Returns the number of the day in the month of the specified date datetime.

DAYOFWEEK

Syntax:DAYOFWEEK( datetime [ , firstday ] )

Returns the day of the week according to ISO 8601.

  • Monday — 1.
  • Sunday — 7.

If the additional parameter firstday is specified, this day is considered the first day of the week. Valid values:

  • "Monday", "Mon" — Monday.
  • "Tuesday". "Tue" — Tuesday.
  • "Wednesday", "Wed" — Wednesday.
  • "Thursday", "Thu" — Thursday.
  • "Friday", "Fri" — Friday.
  • "Saturday", "Sat" — Saturday.
  • "Sunday", "Sun" — Sunday.

DEGREES

Syntax:DEGREES( radians )

Converts radians to degrees.

DIV

Syntax:DIV( number_1, number_2 )

Divides number_1 by number_1. The result is rounded down to the nearest integer.

ENDSWITH

Syntax:ENDSWITH( string, substring )

Returns TRUE if string ends in substring. For case-insensitive searches, see IENDSWITH.

EXP

Syntax:EXP( number )

Returns the result of raising the number 'e' to the power of number.

FIND

Syntax:FIND( string, substring [ , start_index ] )

Returns the index of the position of the first character of the substring substring in the string string.

If the start_index option is specified, the search starts from the specified position.

FLOAT

Syntax:FLOAT( expression )

Converts the expression expression to fractional number format according to the following rules:

Type Value
Number Original value
Date | Datetime Unix time corresponding to the date and time. If the value contains time zone data, it's used in the calculation. If the time zone is unknown, the time is set in UTC
String A number from a decimal string
Boolean TRUE — 1.0, FALSE — 0.0

FLOOR

Syntax:FLOOR( number )

Rounds the value down to the nearest integer.

GEOCODE

Syntax:GEOCODE( address )

Converts address to its geographical coordinates.

GEOPOINT

Syntax:GEOPOINT( value_1 [ , value_2 ] )

Generates a Geopoint type value. For the input, it accepts a string, a "geopoint" type value, or coordinates — latitude value_1 and longitude value_2. If a single string is input, it must contain a list of two numbers (latitude and longitude) in JSON syntax.

GEOPOLYGON

Syntax:GEOPOLYGON( value )

Converts the value expression to geopolygon format.

GREATEST

Syntax:GREATEST( value_1, value_2, value_3 [ , ... ] )

Returns the greatest value.

See also LEAST.

Depending on the specified data type, it returns:

  • The greatest number.
  • The last string in alphabetical order.
  • The latest date.
  • TRUE when selecting between TRUE and FALSE for Boolean type.

HOUR

Syntax:HOUR( datetime )

Returns the number of the hour in the day of the specified date and time datetime. When the date is specified without time, it returns 0.

ICONTAINS

Syntax:ICONTAINS( string, substring )

Case-insensitive version of CONTAINS. Returns TRUE if string contains substring.

IENDSWITH

Syntax:IENDSWITH( string, substring )

Case-insensitive version of ENDSWITH. Returns TRUE if string ends in substring.

IFNULL

Syntax:IFNULL( check_value, alt_value )

Returns check_value if it's not NULL. Otherwise returns alt_value.

IN

Syntax:item [ NOT ] IN (<list>)

Checks whether the value matches at least one of the values listed in IN(...).

The option item NOT IN (<list>) returns the opposite value.

INT

Syntax:INT( expression )

Converts the expression expression to integer format according to the following rules:

Type Value
Number (whole) Original value
Number (decimal) Integer part of the number (rounded down)
Date | Datetime Unix time corresponding to the date and time. If the value contains time zone data, it's used in the calculation. If the time zone is unknown, the time is set in UTC
String A number from a decimal string
Boolean TRUE — 1, FALSE — 0

IS FALSE

Syntax:value IS [ NOT ] FALSE

Checks whether the value value is false (FALSE).

The value IS NOT FALSE option returns the opposite value.

ISNULL

Syntax:
ISNULL( expression )
or
expression IS [ NOT ] NULL

Returns TRUE if expression is NULL, otherwise returns FALSE.

expression IS NOT NULL returns the opposite result.

ISTARTSWITH

Syntax:ISTARTSWITH( string, substring )

Case-insensitive version of STARTSWITH. Returns TRUE if string starts with substring.

IS TRUE

Syntax:value IS [ NOT ] TRUE

Checks whether the value of value is true (TRUE).

The value IS NOT TRUE option returns the opposite value.

LEAST

Syntax:LEAST( value_1, value_2, value_3 [ , ... ] )

Returns the smallest value.

See also GREATEST.

Depending on the specified data type, it returns:

  • The smallest number.
  • The first string in alphabetical order.
  • The earliest date.
  • FALSE when selecting between TRUE and FALSE for Boolean type.

LEFT

Syntax:LEFT( string, number )

Returns a string that contains the number of characters specified in number from the beginning of the string string.

LEN

Syntax:LEN( string )

Returns the number of characters in the string string.

LIKE

Syntax:string_1 [ NOT ] LIKE string_2

Matches the string string_1 to the template string_2 and returns TRUE on match. You can specify the value in string_2 or use the % character to match a string of any length.

The string_1 NOT LIKE option returns the opposite value.

LN

Syntax:LN( number )

Returns the natural logarithm of the number number. Returns 'NULL' if the number is less than or equal to 0.

LOG

Syntax:LOG( value, base )

Returns the logarithm of value to base base. Returns 'NULL' if the number value is less than or equal to 0.

LOG10

Syntax:LOG10( number )

Returns the logarithm of the number number to base 10. Returns 'NULL' if the number is less than or equal to 0.

LOWER

Syntax:LOWER( string )

Returns the string string in lowercase.

LTRIM

Syntax:LTRIM( string )

Returns the string string without spaces at the beginning of the string.

MAX

Syntax:MAX( value )

Returns the maximum value.

If value:

  • number — Returns the largest number.
  • date — Returns the latest date.
  • string — Returns the last value in the alphabetic order.

MEDIAN

Syntax:MEDIAN( value )

Returns the median value.

MIN

Syntax:MIN( value )

Returns the minimum value.

If value:

  • number — Returns the smallest number.
  • date — Returns the earliest date.
  • string — Returns the first value in the alphabetic order.

MINUTE

Syntax:MINUTE( datetime )

Returns the number of the minute in the hour of the specified date datetime. When the date is specified without time, it returns 0.

MONTH

Syntax:MONTH( datetime )

Returns the number of the month in the year of the specified date datetime.

Negation (-)

Syntax:-value

Returns the number value with the opposite sign.

NOT

Syntax:NOT value

Inverts a Boolean value.

NOW

Syntax:NOW()

Returns the current date and time, depending on the data source and connection type:

  • For a direct connection, the function returns the server date and time of the source.
  • On materialization, the function returns the UTC+3 date and time.

OR

Syntax:value_1 OR value_2

Performs a Boolean join of two expressions with the OR condition.

PI

Syntax:PI()

Returns PI. The accuracy depends on the data source.

POWER

Syntax:POWER( base, power )

Raises base to the power of power.

QUANTILE

Syntax:QUANTILE( value, quant )

Returns the precise value-level quantile (value should be in range from 0 to 1).

QUANTILE_APPROX

Syntax:QUANTILE_APPROX( value, quant )

Returns the approximate value-level quantile (value should be in range from 0 to 1).

RADIANS

Syntax:RADIANS( degrees )

Converts degrees degress to radians.

REGEXP_EXTRACT

Syntax:REGEXP_EXTRACT( string, pattern )

Returns the substring string that matches the regular expression pattern pattern.

REGEXP_EXTRACT_NTH

Syntax:REGEXP_EXTRACT_NTH( string, pattern, match_index )

Returns a substring string that matches the regular expression pattern pattern starting from the specified index.

REGEXP_MATCH

Syntax:REGEXP_MATCH( string, pattern )

Returns 'TRUE' if the string string has a substring that matches the regular expression pattern pattern.

REGEXP_REPLACE

Syntax:REGEXP_REPLACE( string, pattern, replace_with )

Searches for a substring in the string string using the regular expression pattern pattern and replaces it with the string replace_with.

If the substring is not found, the string is not changed.

REPLACE

Syntax:REPLACE( string, substring, replace_with )

Searches for the substring substring in the string string and replaces it with the string replace_with.

If the substring is not found, the string is not changed.

Syntax:RIGHT( string, number )

Returns a string that contains the number of characters specified in number from the end of the string string.

ROUND

Syntax:ROUND( number [ , precision ] )

Rounds the number number to the number of decimal digits specified in precision. If the number precision is omitted, number is rounded to the nearest integer.

RTRIM

Syntax:RTRIM( string )

Returns the string string without spaces at the end of the string.

SECOND

Syntax:SECOND( datetime )

Returns the number of the second in the minute of the specified date datetime. When the date is specified without time, it returns 0.

SIGN

Syntax:SIGN( number )

Returns the sign of the number number:

  • -1 if the number is negative. 0 if the number is zero.
  • 1 if the number is positive.

SIN

Syntax:SIN( number )

Returns the sine of number in radians.

SPACE

Syntax:SPACE( value )

Returns a string with the specified number of spaces.

SPLIT

Syntax:SPLIT( orig_string, delimiter, part_index )

Returns a substring from orig_string using the delimiter delimiter character to divide the string into a sequence of part_index parts.

SQRT

Syntax:SQRT( number )

Returns the square root of the specified number.

SQUARE

Syntax:SQUARE( number )

Returns the number number raised to the power of 2.

STARTSWITH

Syntax:STARTSWITH( string, substring )

Returns TRUE if string starts with substring. For case-insensitive searches, see ISTARTSWITH.

STDEV

Syntax:STDEV( value )

Returns the statistical standard deviation of all values in the expression based on a selection from the population.

STDEVP

Syntax:STDEVP( value )

Returns the statistical standard deviation of all values in the expression based on the biased population.

STR

Syntax:STR( expression )

Converts the expression expression to string type.

SUBSTR

Syntax:SUBSTR( string, from_index [ , length ] )

Returns the substring string starting from the index from_index.

If an additional argument length is specified, a substring of the specified length is returned.

SUM

Syntax:SUM( value )

Returns the sum of all expression values. Applicable to numeric data types only.

SUM_IF

Syntax:SUM_IF( expression, condition )

Returns the sum of all the expression values that meet the condition condition. Applicable to numeric data types only.

TAN

Syntax:TAN( number )

Returns the tangent of number in radians.

TODAY

Syntax:TODAY()

Returns the current date, depending on the data source and connection type:

  • For a direct connection, the function returns the server date and time of the source.
  • On materialization, the function returns the UTC+3 date and time.

TRIM

Syntax:TRIM( string )

Returns the string string without spaces at the beginning or end of the string.

UPPER

Syntax:UPPER( string )

Returns the string string in uppercase.

UTF8

Syntax:UTF8( string, old_encoding )

Converts the string string encoding to UTF8.

VAR

Syntax:VAR( value )

Returns the statistical variance of all values in an expression based on a selection from the population.

VARP

Syntax:VARP( value )

Returns the statistical variance of all values in an expression across the entire population.

WEEK

Syntax:WEEK( value )

The number of the week according to ISO 8601. The first week is the week that contains the first Thursday of the year or January 4th.

YEAR

Syntax:YEAR( datetime )

Returns the year number in the specified date datetime.

ZN

Syntax:ZN( expression )

Returns expression if it's not NULL. Otherwise returns 0.