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

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.

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

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

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

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

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.

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

Syntax:`RADIANS( degrees )`

Converts `degrees` degrees 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`.

## REPLACE

Syntax:`REPLACE( string, substring, replace_with )`

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

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.