System (Built-in) Functions

System (Built-in) Functions

Flink Table API & SQL provides users with a set of built-in functions for data transformations. This page gives a brief overview of them. If a function that you need is not supported yet, you can implement a user-defined function. If you think that the function is general enough, please open a Jira issue for it with a detailed description.

Scalar Functions

The scalar functions take zero, one or more values as the input and return a single value as the result.

Comparison Functions

Comparison functions	Description
`value1 = value2`	Returns TRUE if value1 is equal to value2; returns UNKNOWN if value1 or value2 is NULL.
`value1 <> value2`	Returns TRUE if value1 is not equal to value2; returns UNKNOWN if value1 or value2 is NULL.
`value1 > value2`	Returns TRUE if value1 is greater than value2; returns UNKNOWN if value1 or value2 is NULL.
`value1 >= value2`	Returns TRUE if value1 is greater than or equal to value2; returns UNKNOWN if value1 or value2 is NULL.
`value1 < value2`	Returns TRUE if value1 is less than value2; returns UNKNOWN if value1 or value2 is NULL.
`value1 <= value2`	Returns TRUE if value1 is less than or equal to value2; returns UNKNOWN if value1 or value2 is NULL.
`value IS NULL`	Returns TRUE if value is NULL.
`value IS NOT NULL`	Returns TRUE if value is not NULL.
`value1 IS DISTINCT FROM value2`	Returns TRUE if two values are not equal. NULL values are treated as identical here. E.g., `1 IS DISTINCT FROM NULL` returns TRUE; `NULL IS DISTINCT FROM NULL` returns FALSE.
`value1 IS NOT DISTINCT FROM value2`	Returns TRUE if two values are equal. NULL values are treated as identical here. E.g., `1 IS NOT DISTINCT FROM NULL` returns FALSE; `NULL IS NOT DISTINCT FROM NULL` returns TRUE.
`value1 BETWEEN [ ASYMMETRIC \| SYMMETRIC ] value2 AND value3`	By default (or with the ASYMMETRIC keyword), returns TRUE if value1 is greater than or equal to value2 and less than or equal to value3. With the SYMMETRIC keyword, returns TRUE if value1 is inclusively between value2 and value3. When either value2 or value3 is NULL, returns FALSE or UNKNOWN. E.g., `12 BETWEEN 15 AND 12` returns FALSE; `12 BETWEEN SYMMETRIC 15 AND 12` returns TRUE; `12 BETWEEN 10 AND NULL` returns UNKNOWN; `12 BETWEEN NULL AND 10` returns FALSE; `12 BETWEEN SYMMETRIC NULL AND 12` returns UNKNOWN.
`value1 NOT BETWEEN [ ASYMMETRIC \| SYMMETRIC ] value2 AND value3`	By default (or with the ASYMMETRIC keyword), returns TRUE if value1 is less than value2 or greater than value3. With the SYMMETRIC keyword, returns TRUE if value1 is not inclusively between value2 and value3. When either value2 or value3 is NULL, returns TRUE or UNKNOWN. E.g., `12 NOT BETWEEN 15 AND 12` returns TRUE; `12 NOT BETWEEN SYMMETRIC 15 AND 12` returns FALSE; `12 NOT BETWEEN NULL AND 15` returns UNKNOWN; `12 NOT BETWEEN 15 AND NULL` returns TRUE; `12 NOT BETWEEN SYMMETRIC 12 AND NULL` returns UNKNOWN.
`string1 LIKE string2 [ ESCAPE char ]`	Returns TRUE if string1 matches pattern string2; returns UNKNOWN if string1 or string2 is NULL. An escape character can be defined if necessary. Note: The escape character has not been supported yet.
`string1 NOT LIKE string2 [ ESCAPE char ]`	Returns TRUE if string1 does not match pattern string2; returns UNKNOWN if string1 or string2 is NULL. An escape character can be defined if necessary. Note: The escape character has not been supported yet.
`string1 SIMILAR TO string2 [ ESCAPE char ]`	Returns TRUE if string1 matches SQL regular expression string2; returns UNKNOWN if string1 or string2 is NULL. An escape character can be defined if necessary. Note: The escape character has not been supported yet.
`string1 NOT SIMILAR TO string2 [ ESCAPE char ]`	Returns TRUE if string1 does not match SQL regular expression string2; returns UNKNOWN if string1 or string2 is NULL. An escape character can be defined if necessary. Note: The escape character has not been supported yet.
`value1 IN (value2 [, value3] )`	Returns TRUE if value1 exists in the given list (value2, value3, …). When (value2, value3, …). contains NULL, returns TRUE if the element can be found and UNKNOWN otherwise. Always returns UNKNOWN if value1 is NULL. E.g., `4 IN (1, 2, 3)` returns FALSE; `1 IN (1, 2, NULL)` returns TRUE; `4 IN (1, 2, NULL)` returns UNKNOWN.
`value1 NOT IN (value2 [, value3] )`	Returns TRUE if value1 does not exist in the given list (value2, value3, …). When (value2, value3, …). contains NULL, returns FALSE if value1 can be found and UNKNOWN otherwise. Always returns UNKNOWN if value1 is NULL. E.g., `4 NOT IN (1, 2, 3)` returns TRUE; `1 NOT IN (1, 2, NULL)` returns FALSE; `4 NOT IN (1, 2, NULL)` returns UNKNOWN.
`EXISTS (sub-query)`	Returns TRUE if sub-query returns at least one row. Only supported if the operation can be rewritten in a join and group operation. Note: For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See Query Configuration for details.
`value IN (sub-query)`	Returns TRUE if value is equal to a row returned by sub-query. Note: For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See Query Configuration for details.
`value NOT IN (sub-query)`	Returns TRUE if value is not equal to every row returned by sub-query. Note: For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See Query Configuration for details.

Comparison functions	Description
`ANY1 === ANY2`	Returns TRUE if ANY1 is equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 !== ANY2`	Returns TRUE if ANY1 is not equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 > ANY2`	Returns TRUE if ANY1 is greater than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 >= ANY2`	Returns TRUE if ANY1 is greater than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 < ANY2`	Returns TRUE if ANY1 is less than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 <= ANY2`	Returns TRUE if ANY1 is less than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY.isNull`	Returns TRUE if ANY is NULL.
`ANY.isNotNull`	Returns TRUE if ANY is not NULL.
`STRING1.like(STRING2)`	Returns TRUE if STRING1 matches pattern STRING2; returns UNKNOWN if STRING1 or STRING2 is NULL. E.g., `“JoKn”.like(“Jo_n%”)` returns TRUE.
`STRING1.similar(STRING2)`	Returns TRUE if STRING1 matches SQL regular expression STRING2; returns UNKNOWN if STRING1 or STRING2 is NULL. E.g., `“A”.similar(“A+”)` returns TRUE.
`ANY1.in(ANY2, ANY3, …)`	Returns TRUE if ANY1 exists in a given list (ANY2, ANY3, …). When (ANY2, ANY3, …). contains NULL, returns TRUE if the element can be found and UNKNOWN otherwise. Always returns UNKNOWN if ANY1 is NULL. E.g., `4.in(1, 2, 3)` returns FALSE.
`ANY.in(TABLE)`	Returns TRUE if ANY is equal to a row returned by sub-query TABLE. Note: For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See Query Configuration for details.
`ANY1.between(ANY2, ANY3)`	Returns TRUE if ANY1 is greater than or equal to ANY2 and less than or equal to ANY3. When either ANY2 or ANY3 is NULL, returns FALSE or UNKNOWN. E.g., `12.between(15, 12)` returns FALSE; `12.between(10, Null(INT))` returns UNKNOWN; `12.between(Null(INT), 10)` returns FALSE.
`ANY1.notBetween(ANY2, ANY3)`	Returns TRUE if ANY1 is less than ANY2 or greater than ANY3. When either ANY2 or ANY3 is NULL, returns TRUE or UNKNOWN. E.g., `12.notBetween(15, 12)` returns TRUE; `12.notBetween(Null(INT), 15)` returns UNKNOWN; `12.notBetween(15, Null(INT))` returns TRUE.

Comparison functions	Description
`ANY1 === ANY2`	Returns TRUE if ANY1 is equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 !== ANY2`	Returns TRUE if ANY1 is not equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 > ANY2`	Returns TRUE if ANY1 is greater than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 >= ANY2`	Returns TRUE if ANY1 is greater than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 < ANY2`	Returns TRUE if ANY1 is less than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY1 <= ANY2`	Returns TRUE if ANY1 is less than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
`ANY.isNull`	Returns TRUE if ANY is NULL.
`ANY.isNotNull`	Returns TRUE if ANY is not NULL.
`STRING1.like(STRING2)`	Returns TRUE if STRING1 matches pattern STRING2; returns UNKNOWN if STRING1 or STRING2 is NULL. E.g., `“JoKn”.like(“Jo_n%”)` returns TRUE.
`STRING1.similar(STRING2)`	Returns TRUE if STRING1 matches SQL regular expression STRING2; returns UNKNOWN if STRING1 or STRING2 is NULL. E.g., `“A”.similar(“A+”)` returns TRUE.
`ANY1.in(ANY2, ANY3, …)`	Returns TRUE if ANY1 exists in a given list (ANY2, ANY3, …). When (ANY2, ANY3, …). contains NULL, returns TRUE if the element can be found and UNKNOWN otherwise. Always returns UNKNOWN if ANY1 is NULL. E.g., `4.in(1, 2, 3)` returns FALSE.
`ANY.in(TABLE)`	Returns TRUE if ANY is equal to a row returned by sub-query TABLE. Note: For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See Query Configuration for details.
`ANY1.between(ANY2, ANY3)`	Returns TRUE if ANY1 is greater than or equal to ANY2 and less than or equal to ANY3. When either ANY2 or ANY3 is NULL, returns FALSE or UNKNOWN. E.g., `12.between(15, 12)` returns FALSE; `12.between(10, Null(Types.INT))` returns UNKNOWN; `12.between(Null(Types.INT), 10)` returns FALSE.
`ANY1.notBetween(ANY2, ANY3)`	Returns TRUE if ANY1 is less than ANY2 or greater than ANY3. When either ANY2 or ANY3 is NULL, returns TRUE or UNKNOWN. E.g., `12.notBetween(15, 12)` returns TRUE; `12.notBetween(Null(Types.INT), 15)` returns UNKNOWN; `12.notBetween(15, Null(Types.INT))` returns TRUE.

Logical Functions

Logical functions	Description
`boolean1 OR boolean2`	Returns TRUE if boolean1 is TRUE or boolean2 is TRUE. Supports three-valued logic. E.g., `TRUE OR UNKNOWN` returns TRUE.
`boolean1 AND boolean2`	Returns TRUE if boolean1 and boolean2 are both TRUE. Supports three-valued logic. E.g., `TRUE AND UNKNOWN` returns UNKNOWN.
`NOT boolean`	Returns TRUE if boolean is FALSE; returns FALSE if boolean is TRUE; returns UNKNOWN if boolean is UNKNOWN.
`boolean IS FALSE`	Returns TRUE if boolean is FALSE; returns FALSE if boolean is TRUE or UNKNOWN.
`boolean IS NOT FALSE`	Returns TRUE if boolean is TRUE or UNKNOWN; returns FALSE if boolean is FALSE.
`boolean IS TRUE`	Returns TRUE if boolean is TRUE; returns FALSE if boolean is FALSE or UNKNOWN.
`boolean IS NOT TRUE`	Returns TRUE if boolean is FALSE or UNKNOWN; returns FALSE if boolean is TRUE.
`boolean IS UNKNOWN`	Returns TRUE if boolean is UNKNOWN; returns FALSE if boolean is TRUE or FALSE.
`boolean IS NOT UNKNOWN`	Returns TRUE if boolean is TRUE or FALSE; returns FALSE if boolean is UNKNOWN.

Logical functions	Description
`BOOLEAN1 \|\| BOOLEAN2`	Returns TRUE if BOOLEAN1 is TRUE or BOOLEAN2 is TRUE. Supports three-valued logic. E.g., `true \|\| Null(BOOLEAN)` returns TRUE.
`BOOLEAN1 && BOOLEAN2`	Returns TRUE if BOOLEAN1 and BOOLEAN2 are both TRUE. Supports three-valued logic. E.g., `true && Null(BOOLEAN)` returns UNKNOWN.
`!BOOLEAN`	Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE; returns UNKNOWN if BOOLEAN is UNKNOWN.
`BOOLEAN.isTrue`	Returns TRUE if BOOLEAN is TRUE; returns FALSE if BOOLEAN is FALSE or UNKNOWN.
`BOOLEAN.isFalse`	Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE or UNKNOWN.
`BOOLEAN.isNotTrue`	Returns TRUE if BOOLEAN is FALSE or UNKNOWN; returns FALSE if BOOLEAN is TRUE.
`BOOLEAN.isNotFalse`	Returns TRUE if BOOLEAN is TRUE or UNKNOWN; returns FALSE if BOOLEAN is FALSE.

Logical functions	Description
`BOOLEAN1 \|\| BOOLEAN2`	Returns TRUE if BOOLEAN1 is TRUE or BOOLEAN2 is TRUE. Supports three-valued logic. E.g., `true \|\| Null(Types.BOOLEAN)` returns TRUE.
`BOOLEAN1 && BOOLEAN2`	Returns TRUE if BOOLEAN1 and BOOLEAN2 are both TRUE. Supports three-valued logic. E.g., `true && Null(Types.BOOLEAN)` returns UNKNOWN.
`!BOOLEAN`	Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE; returns UNKNOWN if BOOLEAN is UNKNOWN.
`BOOLEAN.isTrue`	Returns TRUE if BOOLEAN is TRUE; returns FALSE if BOOLEAN is FALSE or UNKNOWN.
`BOOLEAN.isFalse`	Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE or UNKNOWN.
`BOOLEAN.isNotTrue`	Returns TRUE if BOOLEAN is FALSE or UNKNOWN; returns FALSE if BOOLEAN is TRUE.
`BOOLEAN.isNotFalse`	Returns TRUE if BOOLEAN is TRUE or UNKNOWN; returns FALSE if BOOLEAN is FALSE.

Arithmetic Functions

Arithmetic functions	Description
`+ numeric`	Returns numeric.
`- numeric`	Returns negative numeric.
`numeric1 + numeric2`	Returns numeric1 plus numeric2.
`numeric1 - numeric2`	Returns numeric1 minus numeric2.
`numeric1 * numeric2`	Returns numeric1 multiplied by numeric2.
`numeric1 / numeric2`	Returns numeric1 divided by numeric2.
`POWER(numeric1, numeric2)`	Returns numeric1 raised to the power of numeric2.
`ABS(numeric)`	Returns the absolute value of numeric.
`MOD(numeric1, numeric2)`	Returns the remainder (modulus) of numeric1 divided by numeric2. The result is negative only if numeric1 is negative.
`SQRT(numeric)`	Returns the square root of numeric.
`LN(numeric)`	Returns the natural logarithm (base e) of numeric.
`LOG10(numeric)`	Returns the base 10 logarithm of numeric.
`LOG2(numeric)`	Returns the base 2 logarithm of numeric.
`LOG(numeric2)` `LOG(numeric1, numeric2)`	When called with one argument, returns the natural logarithm of numeric2. When called with two arguments, this function returns the logarithm of numeric2 to the base numeric1. Note: Currently, numeric2 must be greater than 0 and numeric1 must be greater than 1.
`EXP(numeric)`	Returns e raised to the power of numeric.
`CEIL(numeric)` `CEILING(numeric)`	Rounds numeric up, and returns the smallest number that is greater than or equal to numeric.
`FLOOR(numeric)`	Rounds numeric down, and returns the largest number that is less than or equal to numeric.
`SIN(numeric)`	Returns the sine of numeric.
`SINH(numeric)`	Returns the hyperbolic sine of numeric. The return type is DOUBLE.
`COS(numeric)`	Returns the cosine of numeric.
`TAN(numeric)`	Returns the tangent of numeric.
`TANH(numeric)`	Returns the hyperbolic tangent of numeric. The return type is DOUBLE.
`COT(numeric)`	Returns the cotangent of a numeric.
`ASIN(numeric)`	Returns the arc sine of numeric.
`ACOS(numeric)`	Returns the arc cosine of numeric.
`ATAN(numeric)`	Returns the arc tangent of numeric.
`ATAN2(numeric1, numeric2)`	Returns the arc tangent of a coordinate (numeric1, numeric2).
`COSH(numeric)`	Returns the hyperbolic cosine of NUMERIC. Return value type is DOUBLE.
`DEGREES(numeric)`	Returns the degree representation of a radian numeric.
`RADIANS(numeric)`	Returns the radian representation of a degree numeric.
`SIGN(numeric)`	Returns the signum of numeric.
`ROUND(numeric, integer)`	Returns a number rounded to integer decimal places for numeric.
`PI`	Returns a value that is closer than any other values to pi.
`E()`	Returns a value that is closer than any other values to e.
`RAND()`	Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).
`RAND(integer)`	Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive) with an initial seed integer. Two RAND functions will return identical sequences of numbers if they have the same initial seed.
`RAND_INTEGER(integer)`	Returns a pseudorandom integer value between 0 (inclusive) and integer (exclusive).
`RAND_INTEGER(integer1, integer2)`	Returns a pseudorandom integer value between 0 (inclusive) and the specified value (exclusive) with an initial seed. Two RAND_INTEGER functions will return identical sequences of numbers if they have the same initial seed and bound.
`UUID()`	Returns an UUID (Universally Unique Identifier) string (e.g., “3d3c68f7-f608-473f-b60c-b0c44ad4cc4e”) according to RFC 4122 type 4 (pseudo randomly generated) UUID. The UUID is generated using a cryptographically strong pseudo random number generator.
`BIN(integer)`	Returns a string representation of integer in binary format. Returns NULL if integer is NULL. E.g. `BIN(4)` returns ‘100’ and `BIN(12)` returns ‘1100’.
`HEX(numeric)` `HEX(string)`	Returns a string representation of an integer numeric value or a string in hex format. Returns NULL if the argument is NULL. E.g. a numeric 20 leads to “14”, a numeric 100 leads to “64”, a string “hello,world” leads to “68656C6C6F2C776F726C64”.
`TRUNCATE(numeric1, integer2)`	Returns a numeric of truncated to integer2 decimal places. Returns NULL if numeric1 or integer2 is NULL.If integer2 is 0,the result has no decimal point or fractional part.integer2 can be negative to cause integer2 digits left of the decimal point of the value to become zero.This function can also pass in only one numeric1 parameter and not set Integer2 to use.If Integer2 is not set, the function truncates as if Integer2 were 0. E.g. `truncate(42.345, 2)` to 42.34. and `truncate(42.345)` to 42.0.
`PI()`	Returns the value of π (pi). Only supported in blink planner.

Arithmetic functions	Description
`+ NUMERIC`	Returns NUMERIC.
`- NUMERIC`	Returns negative NUMERIC.
`NUMERIC1 + NUMERIC2`	Returns NUMERIC1 plus NUMERIC2.
`NUMERIC1 - NUMERIC2`	Returns NUMERIC1 minus NUMERIC2.
`NUMERIC1 * NUMERIC2`	Returns NUMERIC1 multiplied by NUMERIC2.
`NUMERIC1 / NUMERIC2`	Returns NUMERIC1 divided by NUMERIC2.
`NUMERIC1.power(NUMERIC2)`	Returns NUMERIC1 raised to the power of NUMERIC2.
`NUMERIC.abs()`	Returns the absolute value of NUMERIC.
`NUMERIC1 % NUMERIC2`	Returns the remainder (modulus) of NUMERIC1 divided by NUMERIC2. The result is negative only if numeric1 is negative.
`NUMERIC.sqrt()`	Returns the square root of NUMERIC.
`NUMERIC.ln()`	Returns the natural logarithm (base e) of NUMERIC.
`NUMERIC.log10()`	Returns the base 10 logarithm of NUMERIC.
`NUMERIC.log2()`	Returns the base 2 logarithm of NUMERIC.
`NUMERIC1.log()` `NUMERIC1.log(NUMERIC2)`	When called without argument, returns the natural logarithm of NUMERIC1. When called with an argument, returns the logarithm of NUMERIC1 to the base NUMERIC2. Note: Currently, NUMERIC1 must be greater than 0 and NUMERIC2 must be greater than 1.
`NUMERIC.exp()`	Returns e raised to the power of NUMERIC.
`NUMERIC.ceil()`	Rounds NUMERIC up, and returns the smallest number that is greater than or equal to NUMERIC.
`NUMERIC.floor()`	Rounds NUMERIC down, and returns the largest number that is less than or equal to NUMERIC.
`NUMERIC.sin()`	Returns the sine of NUMERIC.
`NUMERIC.sinh()`	Returns the hyperbolic sine of NUMERIC. The return type is DOUBLE.
`NUMERIC.cos()`	Returns the cosine of NUMERIC.
`NUMERIC.tan()`	Returns the tangent of NUMERIC.
`NUMERIC.tanh()`	Returns the hyperbolic tangent of NUMERIC. The return type is DOUBLE.
`NUMERIC.cot()`	Returns the cotangent of a NUMERIC.
`NUMERIC.asin()`	Returns the arc sine of NUMERIC.
`NUMERIC.acos()`	Returns the arc cosine of NUMERIC.
`NUMERIC.atan()`	Returns the arc tangent of NUMERIC.
`atan2(NUMERIC1, NUMERIC2)`	Returns the arc tangent of a coordinate (NUMERIC1, NUMERIC2).
`NUMERIC.cosh()`	Returns the hyperbolic cosine of NUMERIC. Return value type is DOUBLE.
`NUMERIC.degrees()`	Returns the degree representation of a radian NUMERIC.
`NUMERIC.radians()`	Returns the radian representation of a degree NUMERIC.
`NUMERIC.sign()`	Returns the signum of NUMERIC.
`NUMERIC.round(INT)`	Returns a number rounded to INT decimal places for NUMERIC.
`pi()`	Returns a value that is closer than any other values to pi.
`e()`	Returns a value that is closer than any other values to e.
`rand()`	Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).
`rand(INTEGER)`	Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive) with an initial seed INTEGER. Two RAND functions will return identical sequences of numbers if they have the same initial seed.
`randInteger(INTEGER)`	Returns a pseudorandom integer value between 0 (inclusive) and INTEGER (exclusive).
`randInteger(INTEGER1, INTEGER2)`	Returns a pseudorandom integer value between 0 (inclusive) and INTEGER2 (exclusive) with an initial seed INTEGER1. Two randInteger functions will return identical sequences of numbers if they have same initial seed and bound.
`uuid()`	Returns an UUID (Universally Unique Identifier) string (e.g., “3d3c68f7-f608-473f-b60c-b0c44ad4cc4e”) according to RFC 4122 type 4 (pseudo randomly generated) UUID. The UUID is generated using a cryptographically strong pseudo random number generator.
`INTEGER.bin()`	Returns a string representation of INTEGER in binary format. Returns NULL if INTEGER is NULL. E.g., `4.bin()` returns “100” and `12.bin()` returns “1100”.
`NUMERIC.hex()` `STRING.hex()`	Returns a string representation of an integer NUMERIC value or a STRING in hex format. Returns NULL if the argument is NULL. E.g. a numeric 20 leads to “14”, a numeric 100 leads to “64”, a string “hello,world” leads to “68656C6C6F2C776F726C64”.
`numeric1.truncate(INTEGER2)`	Returns a numeric of truncated to integer2 decimal places. Returns NULL if numeric1 or integer2 is NULL.If integer2 is 0,the result has no decimal point or fractional part.integer2 can be negative to cause integer2 digits left of the decimal point of the value to become zero.This function can also pass in only one numeric1 parameter and not set Integer2 to use.If Integer2 is not set, the function truncates as if Integer2 were 0. E.g. `42.324.truncate(2)` to 42.34. and `42.324.truncate()` to 42.0.

Arithmetic functions	Description
`+ NUMERIC`	Returns NUMERIC.
`- NUMERIC`	Returns negative NUMERIC.
`NUMERIC1 + NUMERIC2`	Returns NUMERIC1 plus NUMERIC2.
`NUMERIC1 - NUMERIC2`	Returns NUMERIC1 minus NUMERIC2.
`NUMERIC1 * NUMERIC2`	Returns NUMERIC1 multiplied by NUMERIC2.
`NUMERIC1 / NUMERIC2`	Returns NUMERIC1 divided by NUMERIC2.
`NUMERIC1.power(NUMERIC2)`	Returns NUMERIC1 raised to the power of NUMERIC2.
`NUMERIC.abs()`	Returns the absolute value of NUMERIC.
`NUMERIC1 % NUMERIC2`	Returns the remainder (modulus) of NUMERIC1 divided by NUMERIC2. The result is negative only if numeric1 is negative.
`NUMERIC.sqrt()`	Returns the square root of NUMERIC.
`NUMERIC.ln()`	Returns the natural logarithm (base e) of NUMERIC.
`NUMERIC.log10()`	Returns the base 10 logarithm of NUMERIC.
`NUMERIC.log2()`	Returns the base 2 logarithm of NUMERIC.
`NUMERIC1.log()` `NUMERIC1.log(NUMERIC2)`	When called without argument, returns the natural logarithm of NUMERIC1. When called with an argument, returns the logarithm of NUMERIC1 to the base NUMERIC2. Note: Currently, NUMERIC1 must be greater than 0 and NUMERIC2 must be greater than 1.
`NUMERIC.exp()`	Returns e raised to the power of NUMERIC.
`NUMERIC.ceil()`	Rounds NUMERIC up, and returns the smallest number that is greater than or equal to NUMERIC.
`NUMERIC.floor()`	Rounds NUMERIC down, and returns the largest number that is less than or equal to NUMERIC.
`NUMERIC.sin()`	Returns the sine of NUMERIC.
`NUMERIC.sinh()`	Returns the hyperbolic sine of NUMERIC. The return type is DOUBLE.
`NUMERIC.cos()`	Returns the cosine of NUMERIC.
`NUMERIC.tan()`	Returns the tangent of NUMERIC.
`NUMERIC.tanh()`	Returns the hyperbolic tangent of NUMERIC. The return type is DOUBLE.
`NUMERIC.cot()`	Returns the cotangent of a NUMERIC.
`NUMERIC.asin()`	Returns the arc sine of NUMERIC.
`NUMERIC.acos()`	Returns the arc cosine of NUMERIC.
`NUMERIC.atan()`	Returns the arc tangent of NUMERIC.
`atan2(NUMERIC1, NUMERIC2)`	Returns the arc tangent of a coordinate (NUMERIC1, NUMERIC2).
`NUMERIC.cosh()`	Returns the hyperbolic cosine of NUMERIC. Return value type is DOUBLE.
`NUMERIC.degrees()`	Returns the degree representation of a radian NUMERIC.
`NUMERIC.radians()`	Returns the radian representation of a degree NUMERIC.
`NUMERIC.sign()`	Returns the signum of NUMERIC.
`NUMERIC.round(INT)`	Returns a number rounded to INT decimal places for NUMERIC.
`pi()`	Returns a value that is closer than any other values to pi.
`e()`	Returns a value that is closer than any other values to e.
`rand()`	Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).
`rand(INTEGER)`	Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive) with an initial seed INTEGER. Two RAND functions will return identical sequences of numbers if they have the same initial seed.
`randInteger(INTEGER)`	Returns a pseudorandom integer value between 0 (inclusive) and INTEGER (exclusive).
`randInteger(INTEGER1, INTEGER2)`	Returns a pseudorandom integer value between 0 (inclusive) and INTEGER2 (exclusive) with an initial seed INTEGER1. Two randInteger functions will return identical sequences of numbers if they have same initial seed and bound.
`uuid()`	Returns an UUID (Universally Unique Identifier) string (e.g., “3d3c68f7-f608-473f-b60c-b0c44ad4cc4e”) according to RFC 4122 type 4 (pseudo randomly generated) UUID. The UUID is generated using a cryptographically strong pseudo random number generator.
`INTEGER.bin()`	Returns a string representation of INTEGER in binary format. Returns NULL if INTEGER is NULL. E.g., `4.bin()` returns “100” and `12.bin()` returns “1100”.
`NUMERIC.hex()` `STRING.hex()`	Returns a string representation of an integer NUMERIC value or a STRING in hex format. Returns NULL if the argument is NULL. E.g. a numeric 20 leads to “14”, a numeric 100 leads to “64”, a string “hello,world” leads to “68656C6C6F2C776F726C64”.

String Functions

String functions	Description
`string1 \|\| string2`	Returns the concatenation of string1 and string2.
`CHAR_LENGTH(string)` `CHARACTER_LENGTH(string)`	Returns the number of characters in string.
`UPPER(string)`	Returns string in uppercase.
`LOWER(string)`	Returns string in lowercase.
`POSITION(string1 IN string2)`	Returns the position (start from 1) of the first occurrence of string1 in string2; returns 0 if string1 cannot be found in string2.
`TRIM([ BOTH \| LEADING \| TRAILING ] string1 FROM string2)`	Returns a string that removes leading and/or trailing characters string1 from string2. By default, whitespaces at both sides are removed.
`LTRIM(string)`	Returns a string that removes the left whitespaces from string. E.g., `LTRIM(‘ This is a test String.’)` returns “This is a test String.”.
`RTRIM(string)`	Returns a string that removes the right whitespaces from string. E.g., `RTRIM(‘This is a test String. ‘)` returns “This is a test String.”.
`REPEAT(string, integer)`	Returns a string that repeats the base string integer times. E.g., `REPEAT(‘This is a test String.’, 2)` returns “This is a test String.This is a test String.”.
`REGEXP_REPLACE(string1, string2, string3)`	Returns a string from string1 with all the substrings that match a regular expression string2 consecutively being replaced with string3. E.g., `REGEXP_REPLACE(‘foobar’, ‘oo\|ar’, ‘’)` returns “fb”.
`OVERLAY(string1 PLACING string2 FROM integer1 [ FOR integer2 ])`	Returns a string that replaces integer2 (string2‘s length by default) characters of string1 with string2 from position integer1. E.g., `OVERLAY(‘This is an old string’ PLACING ‘ new’ FROM 10 FOR 5)` returns “This is a new string”
`SUBSTRING(string FROM integer1 [ FOR integer2 ])`	Returns a substring of string starting from position integer1 with length integer2 (to the end by default).
`REPLACE(string1, string2, string3)`	Returns a new string which replaces all the occurrences of string2 with string3 (non-overlapping) from string1 E.g., `REPLACE(‘hello world’, ‘world’, ‘flink’)` returns “hello flink”; `REPLACE(‘ababab’, ‘abab’, ‘z’)` returns “zab”.
`REGEXP_EXTRACT(string1, string2[, integer])`	Returns a string from string1 which extracted with a specified regular expression string2 and a regex match group index integer. Note: The regex match group index starts from 1 and 0 means matching the whole regex. In addition, the regex match group index should not exceed the number of the defined groups. E.g. `REGEXP_EXTRACT(‘foothebar’, ‘foo(.*?)(bar)’, 2)”` returns “bar”.
`INITCAP(string)`	Returns a new form of string with the first character of each word converted to uppercase and the rest characters to lowercase. Here a word means a sequences of alphanumeric characters.
`CONCAT(string1, string2,…)`	Returns a string that concatenates string1, string2, …. Returns NULL if any argument is NULL. E.g., `CONCAT(‘AA’, ‘BB’, ‘CC’)` returns “AABBCC”.
`CONCAT_WS(string1, string2, string3,…)`	Returns a string that concatenates string2, string3, … with a separator string1. The separator is added between the strings to be concatenated. Returns NULL If string1 is NULL. Compared with `CONCAT()`, `CONCAT_WS()` automatically skips NULL arguments. E.g., `CONCAT_WS(‘~’, ‘AA’, NULL, ‘BB’, ‘’, ‘CC’)` returns “AA~BB~~CC”.
`LPAD(string1, integer, string2)`	Returns a new string from string1 left-padded with string2 to a length of integer characters. If the length of string1 is shorter than integer, returns string1 shortened to integer characters. E.g., `LPAD(‘hi’,4,’??’)` returns “??hi”; `LPAD(‘hi’,1,’??’)` returns “h”.
`RPAD(string1, integer, string2)`	Returns a new string from string1 right-padded with string2 to a length of integer characters. If the length of string1 is shorter than integer, returns string1 shortened to integer characters. E.g., `RPAD(‘hi’,4,’??’)` returns “hi??”, `RPAD(‘hi’,1,’??’)` returns “h”.
`FROM_BASE64(string)`	Returns the base64-decoded result from string; returns NULL if string is NULL. E.g., `FROM_BASE64(‘aGVsbG8gd29ybGQ=’)` returns “hello world”.
`TO_BASE64(string)`	Returns the base64-encoded result from string; returns NULL if string is NULL. E.g., `TO_BASE64(‘hello world’)` returns “aGVsbG8gd29ybGQ=”.
`ASCII(string)`	Returns the numeric value of the first character of string. Returns NULL if string is NULL. Only supported in blink planner. E.g., `ascii(‘abc’)` returns 97, and `ascii(CAST(NULL AS VARCHAR))` returns NULL.
`CHR(integer)`	Returns the ASCII character having the binary equivalent to integer. If integer is larger than 255, we will get the modulus of integer divided by 255 first, and returns CHR of the modulus. Returns NULL if integer is NULL. Only supported in blink planner. E.g., `chr(97)` returns a, `chr(353)` returns a, and `ascii(CAST(NULL AS VARCHAR))` returns NULL.
`DECODE(binary, string)`	Decodes the first argument into a String using the provided character set (one of ‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’). If either argument is null, the result will also be null. Only supported in blink planner.
`ENCODE(string1, string2)`	Encodes the string1 into a BINARY using the provided string2 character set (one of ‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’). If either argument is null, the result will also be null. Only supported in blink planner.
`INSTR(string1, string2)`	Returns the position of the first occurrence of string2 in string1. Returns NULL if any of arguments is NULL.</p> Only supported in blink planner.
`LEFT(string, integer)`	Returns the leftmost integer characters from the string. Returns EMPTY String if integer is negative. Returns NULL if any argument is NULL. Only supported in blink planner.
`RIGHT(string, integer)`	Returns the rightmost integer characters from the string. Returns EMPTY String if integer is negative. Returns NULL if any argument is NULL. Only supported in blink planner.
`LOCATE(string1, string2[, integer])`	Returns the position of the first occurrence of string1 in string2 after position integer. Returns 0 if not found. Returns NULL if any of arguments is NULL. Only supported in blink planner.
`PARSE_URL(string1, string2[, string3])`	Returns the specified part from the URL. Valid values for string2 include ‘HOST’, ‘PATH’, ‘QUERY’, ‘REF’, ‘PROTOCOL’, ‘AUTHORITY’, ‘FILE’, and ‘USERINFO’. Returns NULL if any of arguments is NULL. E.g., `parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1‘, ‘HOST’)`, returns ‘facebook.com’. Also a value of a particular key in QUERY can be extracted by providing the key as the third argument string3. E.g., `parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1‘, ‘QUERY’, ‘k1’)` returns ‘v1’. Only supported in blink planner.
`REGEXP(string1, string2)`	Returns TRUE if any (possibly empty) substring of string1 matches the Java regular expression string2, otherwise FALSE. Returns NULL if any of arguments is NULL. Only supported in blink planner.
`REVERSE(string)`	Returns the reversed string. Returns NULL if string is NULL. Only supported in blink planner.
`SPLIT_INDEX(string1, string2, integer1)`	Splits string1 by the delimiter string2, returns the integerth (zero-based) string of the split strings. Returns NULL if integer is negative. Returns NULL if any of arguments is NULL. Only supported in blink planner.
`STR_TO_MAP(string1[, string2, string3]])`	Returns a map after splitting the string1 into key/value pairs using delimiters. string2 is the pair delimiter, default is ‘,’. And string3 is the key-value delimiter, default is ‘=’. Only supported in blink planner.
`SUBSTR(string[, integer1[, integer2]])`	Returns a substring of string starting from position integer1 with length integer2 (to the end by default). Only supported in blink planner.

String functions	Description
`STRING1 + STRING2`	Returns the concatenation of STRING1 and STRING2.
`STRING.charLength()`	Returns the number of characters in STRING.
`STRING.upperCase()`	Returns STRING in uppercase.
`STRING.lowerCase()`	Returns STRING in lowercase.
`STRING1.position(STRING2)`	Returns the position (start from 1) of the first occurrence of STRING1 in STRING2; returns 0 if STRING1 cannot be found in STRING2.
`STRING1.trim(LEADING, STRING2)` `STRING1.trim(TRAILING, STRING2)` `STRING1.trim(BOTH, STRING2)` `STRING1.trim(BOTH)` `STRING1.trim()`	Returns a string that removes leading and/or trailing characters STRING2 from STRING1. By default, whitespaces at both sides are removed.
`STRING.ltrim()`	Returns a string that removes the left whitespaces from STRING. E.g., `‘ This is a test String.’.ltrim()` returns “This is a test String.”.
`STRING.rtrim()`	Returns a string that removes the right whitespaces from STRING. E.g., `‘This is a test String. ‘.rtrim()` returns “This is a test String.”.
`STRING.repeat(INT)`	Returns a string that repeats the base STRING INT times. E.g., `‘This is a test String.’.repeat(2)` returns “This is a test String.This is a test String.”.
`STRING1.regexpReplace(STRING2, STRING3)`	Returns a string from STRING1 with all the substrings that match a regular expression STRING2 consecutively being replaced with STRING3. E.g., `‘foobar’.regexpReplace(‘oo\|ar’, ‘’)` returns “fb”.
`STRING1.overlay(STRING2, INT1)` `STRING1.overlay(STRING2, INT1, INT2)`	Returns a string that replaces INT2 (STRING2‘s length by default) characters of STRING1 with STRING2 from position INT1. E.g., `‘xxxxxtest’.overlay(‘xxxx’, 6)` returns “xxxxxxxxx”; `‘xxxxxtest’.overlay(‘xxxx’, 6, 2)` returns “xxxxxxxxxst”.
`STRING.substring(INT1)` `STRING.substring(INT1, INT2)`	Returns a substring of STRING starting from position INT1 with length INT2 (to the end by default).
`STRING1.replace(STRING2, STRING3)`	Returns a new string which replaces all the occurrences of STRING2 with STRING3 (non-overlapping) from STRING1. E.g., `‘hello world’.replace(‘world’, ‘flink’)` returns ‘hello flink’; `‘ababab’.replace(‘abab’, ‘z’)` returns ‘zab’.
`STRING1.regexpExtract(STRING2[, INTEGER1])`	Returns a string from STRING1 which extracted with a specified regular expression STRING2 and a regex match group index INTEGER1. Note: The regex match group index starts from 1 and 0 means matching the whole regex. In addition, the regex match group index should not exceed the number of the defined groups. E.g., `‘foothebar’.regexpExtract(‘foo(.*?)(bar)’, 2)` returns “bar”.
`STRING.initCap()`	Returns a new form of STRING with the first character of each word converted to uppercase and the rest characters to lowercase. Here a word means a sequences of alphanumeric characters.
`concat(STRING1, STRING2, …)`	Returns a string that concatenates STRING1, STRING2, …. Returns NULL if any argument is NULL. E.g., `concat(‘AA’, ‘BB’, ‘CC’)` returns “AABBCC”.
`concat_ws(STRING1, STRING2, STRING3, …)`	Returns a string that concatenates STRING2, STRING3, … with a separator STRING1. The separator is added between the strings to be concatenated. Returns NULL If STRING1 is NULL. Compared with `concat()`, `concat_ws()` automatically skips NULL arguments. E.g., `concat_ws(‘~’, ‘AA’, Null(STRING), ‘BB’, ‘’, ‘CC’)` returns “AA~BB~~CC”.
`STRING1.lpad(INT, STRING2)`	Returns a new string from STRING1 left-padded with STRING2 to a length of INT characters. If the length of STRING1 is shorter than INT, returns STRING1 shortened to INT characters. E.g., `‘hi’.lpad(4, ‘??’)` returns “??hi”; `‘hi’.lpad(1, ‘??’)` returns “h”.
`STRING1.rpad(INT, STRING2)`	Returns a new string from STRING1 right-padded with STRING2 to a length of INT characters. If the length of STRING1 is shorter than INT, returns STRING1 shortened to INT characters. E.g., `‘hi’.rpad(4, ‘??’)` returns “hi??”; `‘hi’.rpad(1, ‘??’)` returns “h”.
`STRING.fromBase64()`	Returns the base64-decoded result from STRING; returns NULL if STRING is NULL. E.g., `‘aGVsbG8gd29ybGQ=’.fromBase64()` returns “hello world”.
`STRING.toBase64()`	Returns the base64-encoded result from STRING; returns NULL if STRING is NULL. E.g., `‘hello world’.toBase64()` returns “aGVsbG8gd29ybGQ=”.

String functions	Description
`STRING1 + STRING2`	Returns the concatenation of STRING1 and STRING2.
`STRING.charLength()`	Returns the number of characters in STRING.
`STRING.upperCase()`	Returns STRING in uppercase.
`STRING.lowerCase()`	Returns STRING in lowercase.
`STRING1.position(STRING2)`	Returns the position (start from 1) of the first occurrence of STRING1 in STRING2; returns 0 if STRING1 cannot be found in STRING2.
`STRING.trim(` `leading = true,` `trailing = true,` `character = “ “)`	Returns a string that removes leading and/or trailing characters from STRING.
`STRING.ltrim()`	Returns a string that removes the left whitespaces from STRING. E.g., `“ This is a test String.”.ltrim()` returns “This is a test String.”.
`STRING.rtrim()`	Returns a string that removes the right whitespaces from STRING. E.g., `“This is a test String. “.rtrim()` returns “This is a test String.”.
`STRING.repeat(INT)`	Returns a string that repeats the base STRING INT times. E.g., `“This is a test String.”.repeat(2)` returns “This is a test String.This is a test String.”.
`STRING1.regexpReplace(STRING2, STRING3)`	Returns a string from STRING1 with all the substrings that match a regular expression STRING2 consecutively being replaced with STRING3. E.g. `“foobar”.regexpReplace(“oo\|ar”, “”)` returns “fb”.
`STRING1.overlay(STRING2, INT1)` `STRING1.overlay(STRING2, INT1, INT2)`	Returns a string that replaces INT2 (STRING2‘s length by default) characters of STRING1 with STRING2 from position INT1. E.g., `“xxxxxtest”.overlay(“xxxx”, 6)` returns “xxxxxxxxx”; `“xxxxxtest”.overlay(“xxxx”, 6, 2)` returns “xxxxxxxxxst”.
`STRING.substring(INT1)` `STRING.substring(INT1, INT2)`	Returns a substring of STRING starting from position INT1 with length INT2 (to the end by default).
`STRING1.replace(STRING2, STRING3)`	Returns a new string which replaces all the occurrences of STRING2 with STRING3 (non-overlapping) from STRING1. E.g., `“hello world”.replace(“world”, “flink”)` returns “hello flink”; `“ababab”.replace(“abab”, “z”)` returns “zab”.
`STRING1.regexpExtract(STRING2[, INTEGER1])`	Returns a string from STRING1 which extracted with a specified regular expression STRING2 and a regex match group index INTEGER1. Note: The regex match group index starts from 1 and 0 means matching the whole regex. In addition, the regex match group index should not exceed the number of the defined groups. E.g. `“foothebar”.regexpExtract(“foo(.*?)(bar)”, 2)”` returns “bar”.
`STRING.initCap()`	Returns a new form of STRING with the first character of each word converted to uppercase and the rest characters to lowercase. Here a word means a sequences of alphanumeric characters.
`concat(STRING1, STRING2, …)`	Returns a string that concatenates STRING1, STRING2, …. Returns NULL if any argument is NULL. E.g., `concat(“AA”, “BB”, “CC”)` returns “AABBCC”.
`concat_ws(STRING1, STRING2, STRING3, …)`	Returns a string that concatenates STRING2, STRING3, … with a separator STRING1. The separator is added between the strings to be concatenated. Returns NULL If STRING1 is NULL. Compared with `concat()`, `concat_ws()` automatically skips NULL arguments. E.g., `concat_ws(“~”, “AA”, Null(Types.STRING), “BB”, “”, “CC”)` returns “AA~BB~~CC”.
`STRING1.lpad(INT, STRING2)`	Returns a new string from STRING1 left-padded with STRING2 to a length of INT characters. If the length of STRING1 is shorter than INT, returns STRING1 shortened to INT characters. E.g., `“hi”.lpad(4, “??”)` returns “??hi”; `“hi”.lpad(1, “??”)` returns “h”.
`STRING1.rpad(INT, STRING2)`	Returns a new string from STRING1 right-padded with STRING2 to a length of INT characters. If the length of STRING1 is shorter than INT, returns STRING1 shortened to INT characters. E.g., `“hi”.rpad(4, “??”)` returns “hi??”; `“hi”.rpad(1, “??”)` returns “h”.
`STRING.fromBase64()`	Returns the base64-decoded result from STRING; returns null If STRING is NULL. E.g., `“aGVsbG8gd29ybGQ=”.fromBase64()` returns “hello world”.
`STRING.toBase64()`	Returns the base64-encoded result from STRING; returns NULL if STRING is NULL. E.g., `“hello world”.toBase64()` returns “aGVsbG8gd29ybGQ=”.

Temporal Functions

Temporal functions	Description
`DATE string`	Returns a SQL date parsed from string in form of “yyyy-MM-dd”.
`TIME string`	Returns a SQL time parsed from string in form of “HH:mm:ss”.
`TIMESTAMP string`	Returns a SQL timestamp parsed from string in form of “yyyy-MM-dd HH:mm:ss[.SSS]”.
`INTERVAL string range`	Parses an interval string in the form “dd hh:mm:ss.fff” for SQL intervals of milliseconds or “yyyy-mm” for SQL intervals of months. An interval range might be `DAY`, `MINUTE`, `DAY TO HOUR`, or `DAY TO SECOND` for intervals of milliseconds; `YEAR` or `YEAR TO MONTH` for intervals of months. E.g., `INTERVAL ‘10 00:00:00.004’ DAY TO SECOND`, `INTERVAL ‘10’ DAY`, or `INTERVAL ‘2-10’ YEAR TO MONTH` return intervals.
`CURRENT_DATE`	Returns the current SQL date in the UTC time zone.
`CURRENT_TIME`	Returns the current SQL time in the UTC time zone.
`CURRENT_TIMESTAMP`	Returns the current SQL timestamp in the UTC time zone.
`LOCALTIME`	Returns the current SQL time in local time zone.
`LOCALTIMESTAMP`	Returns the current SQL timestamp in local time zone.
`EXTRACT(timeintervalunit FROM temporal)`	Returns a long value extracted from the timeintervalunit part of temporal. E.g., `EXTRACT(DAY FROM DATE ‘2006-06-05’)` returns 5.
`YEAR(date)`	Returns the year from SQL date date. Equivalent to EXTRACT(YEAR FROM date). E.g., `YEAR(DATE ‘1994-09-27’)` returns 1994.
`QUARTER(date)`	Returns the quarter of a year (an integer between 1 and 4) from SQL date date. Equivalent to `EXTRACT(QUARTER FROM date)`. E.g., `QUARTER(DATE ‘1994-09-27’)` returns 3.
`MONTH(date)`	Returns the month of a year (an integer between 1 and 12) from SQL date date. Equivalent to `EXTRACT(MONTH FROM date)`. E.g., `MONTH(DATE ‘1994-09-27’)` returns 9.
`WEEK(date)`	Returns the week of a year (an integer between 1 and 53) from SQL date date. Equivalent to `EXTRACT(WEEK FROM date)`. E.g., `WEEK(DATE ‘1994-09-27’)` returns 39.
`DAYOFYEAR(date)`	Returns the day of a year (an integer between 1 and 366) from SQL date date. Equivalent to `EXTRACT(DOY FROM date)`. E.g., `DAYOFYEAR(DATE ‘1994-09-27’)` returns 270.
`DAYOFMONTH(date)`	Returns the day of a month (an integer between 1 and 31) from SQL date date. Equivalent to `EXTRACT(DAY FROM date)`. E.g., `DAYOFMONTH(DATE ‘1994-09-27’)` returns 27.
`DAYOFWEEK(date)`	Returns the day of a week (an integer between 1 and 7; Sunday = 1) from SQL date date.Equivalent to `EXTRACT(DOW FROM date)`. E.g., `DAYOFWEEK(DATE ‘1994-09-27’)` returns 3.
`HOUR(timestamp)`	Returns the hour of a day (an integer between 0 and 23) from SQL timestamp timestamp. Equivalent to `EXTRACT(HOUR FROM timestamp)`. E.g., `HOUR(TIMESTAMP ‘1994-09-27 13:14:15’)` returns 13.
`MINUTE(timestamp)`	Returns the minute of an hour (an integer between 0 and 59) from SQL timestamp timestamp. Equivalent to `EXTRACT(MINUTE FROM timestamp)`. E.g., `MINUTE(TIMESTAMP ‘1994-09-27 13:14:15’)` returns 14.
`SECOND(timestamp)`	Returns the second of a minute (an integer between 0 and 59) from SQL timestamp. Equivalent to `EXTRACT(SECOND FROM timestamp)`. E.g., `SECOND(TIMESTAMP ‘1994-09-27 13:14:15’)` returns 15.
`FLOOR(timepoint TO timeintervalunit)`	Returns a value that rounds timepoint down to the time unit timeintervalunit. E.g., `FLOOR(TIME ‘12:44:31’ TO MINUTE)` returns 12:44:00.
`CEIL(timepoint TO timeintervalunit)`	Returns a value that rounds timepoint up to the time unit timeintervalunit. E.g., `CEIL(TIME ‘12:44:31’ TO MINUTE)` returns 12:45:00.
`(timepoint1, temporal1) OVERLAPS (timepoint2, temporal2)`	Returns TRUE if two time intervals defined by (timepoint1, temporal1) and (timepoint2, temporal2) overlap. The temporal values could be either a time point or a time interval. E.g., `(TIME ‘2:55:00’, INTERVAL ‘1’ HOUR) OVERLAPS (TIME ‘3:30:00’, INTERVAL ‘2’ HOUR)` returns TRUE; `(TIME ‘9:00:00’, TIME ‘10:00:00’) OVERLAPS (TIME ‘10:15:00’, INTERVAL ‘3’ HOUR)` returns FALSE.
`DATE_FORMAT(timestamp, string)`	Attention for old planner This function has serious bugs and should not be used for now. Please implement a custom UDF instead or use EXTRACT as a workaround. For blink planner, this converts timestamp to a value of string in the format specified by the date format string. The format string is compatible with Java’s SimpleDateFormat.
`TIMESTAMPADD(timeintervalunit, interval, timepoint)`	Returns a new time value that adds a (signed) integer interval to timepoint. The unit for interval is given by the unit argument, which should be one of the following values: `SECOND`, `MINUTE`, `HOUR`, `DAY`, `WEEK`, `MONTH`, `QUARTER`, or `YEAR`. E.g., `TIMESTAMPADD(WEEK, 1, DATE ‘2003-01-02’)` returns `2003-01-09`.
`TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2)`	Returns the (signed) number of timepointunit between timepoint1 and timepoint2. The unit for the interval is given by the first argument, which should be one of the following values: `SECOND`, `MINUTE`, `HOUR`, `DAY`, `MONTH`, or `YEAR`. See also the Time Interval and Point Unit Specifiers table. E.g., `TIMESTAMPDIFF(DAY, TIMESTAMP ‘2003-01-02 10:00:00’, TIMESTAMP ‘2003-01-03 10:00:00’)` leads to `1`.
`CONVERT_TZ(string1, string2, string3)`	Converts a datetime string1 (with default ISO timestamp format ‘yyyy-MM-dd HH:mm:ss’) from time zone string2 to time zone string3. The format of time zone should be either an abbreviation such as “PST”, a full name such as “America/Los_Angeles”, or a custom ID such as “GMT-8:00”. E.g., `CONVERT(‘1970-01-01 00:00:00’, ‘UTC’, ‘America/Los_Angeles’)` returns ‘1969-12-31 16:00:00’. Only supported in blink planner.
`FROM_UNIXTIME(numeric[, string])`	Returns a representation of the numeric argument as a value in string format (default is ‘YYYY-MM-DD hh:mm:ss’). numeric is an internal timestamp value representing seconds since ‘1970-01-01 00:00:00’ UTC, such as produced by the UNIX_TIMESTAMP() function. The return value is expressed in the session time zone (specified in TableConfig). E.g., `FROM_UNIXTIME(44)` returns ‘1970-01-01 09:00:44’ if in UTC time zone, but returns ‘1970-01-01 09:00:44’ if in ‘Asia/Tokyo’ time zone. Only supported in blink planner.
`UNIX_TIMESTAMP()`	Gets current Unix timestamp in seconds. This function is not deterministic. Only supported in blink planner.
`UNIX_TIMESTAMP(string1[, string2])`	Converts date time string string1 in format string2 (by default: yyyy-MM-dd HH:mm:ss if not specified) to Unix timestamp (in seconds), using the specified timezone in table config. Only supported in blink planner.
`TO_DATE(string1[, string2])`	Converts a date string string1 with format string2 (by default ‘yyyy-MM-dd’) to a date. Only supported in blink planner.
`TO_TIMESTAMP(string1[, string2])`	Converts date time string string1 with format string2 (by default: ‘yyyy-MM-dd HH:mm:ss’) under the session time zone (specified by TableConfig) to a timestamp. Only supported in blink planner.
`NOW()`	Returns the current SQL timestamp in the UTC time zone. This function is not deterministic. Only supported in blink planner.

Temporal functions	Description
`STRING.toDate()`	Returns a SQL date parsed from STRING in form of “yyyy-MM-dd”.
`STRING.toTime()`	Returns a SQL time parsed from STRING in form of “HH:mm:ss”.
`STRING.toTimestamp()`	Returns a SQL timestamp parsed from STRING in form of “yyyy-MM-dd HH:mm:ss[.SSS]”.
`NUMERIC.year` `NUMERIC.years`	Creates an interval of months for NUMERIC years.
`NUMERIC.quarter` `NUMERIC.quarters`	Creates an interval of months for NUMERIC quarters. E.g., `2.quarters` returns 6.
`NUMERIC.month` `NUMERIC.months`	Creates an interval of NUMERIC months.
`NUMERIC.week` `NUMERIC.weeks`	Creates an interval of milliseconds for NUMERIC weeks. E.g., `2.weeks` returns 1209600000.
`NUMERIC.day` `NUMERIC.days`	Creates an interval of milliseconds for NUMERIC days.
`NUMERIC.hour` `NUMERIC.hours`	Creates an interval of milliseconds for NUMERIC hours.
`NUMERIC.minute` `NUMERIC.minutes`	Creates an interval of milliseconds for NUMERIC minutes.
`NUMERIC.second` `NUMERIC.seconds`	Creates an interval of milliseconds for NUMERIC seconds.
`NUMERIC.milli` `NUMERIC.millis`	Creates an interval of NUMERIC milliseconds.
`currentDate()`	Returns the current SQL date in the UTC time zone.
`currentTime()`	Returns the current SQL time in the UTC time zone.
`currentTimestamp()`	Returns the current SQL timestamp in the UTC time zone.
`localTime()`	Returns the current SQL time in local time zone.
`localTimestamp()`	Returns the current SQL timestamp in local time zone.
`TEMPORAL.extract(TIMEINTERVALUNIT)`	Returns a long value extracted from the TIMEINTERVALUNIT part of temporal. E.g., `‘2006-06-05’.toDate.extract(DAY)` returns 5; `‘2006-06-05’.toDate.extract(QUARTER)` returns 2.
`TIMEPOINT.floor(TIMEINTERVALUNIT)`	Returns a value that rounds TIMEPOINT down to the time unit TIMEINTERVALUNIT. E.g., `‘12:44:31’.toDate.floor(MINUTE)` returns 12:44:00.
`TIMEPOINT.ceil(TIMEINTERVALUNIT)`	Returns a value that rounds TIMEPOINT up to the time unit TIMEINTERVALUNIT. E.g., `‘12:44:31’.toTime.floor(MINUTE)` returns 12:45:00.
`temporalOverlaps(TIMEPOINT1, TEMPORAL1, TIMEPOINT2, TEMPORAL2)`	Returns TRUE if two time intervals defined by (TIMEPOINT1, TEMPORAL1) and (TIMEPOINT2, TEMPORAL2) overlap. The temporal values could be either a time point or a time interval. E.g., `temporalOverlaps(‘2:55:00’.toTime, 1.hour, ‘3:30:00’.toTime, 2.hour)` returns TRUE.
`dateFormat(TIMESTAMP, STRING)`	Attention This function has serious bugs and should not be used for now. Please implement a custom UDF instead or use extract() as a workaround.
`timestampDiff(TIMEPOINTUNIT, TIMEPOINT1, TIMEPOINT2)`	Returns the (signed) number of TIMEPOINTUNIT between TIMEPOINT1 and TIMEPOINT2. The unit for the interval is given by the first argument, which should be one of the following values: `SECOND`, `MINUTE`, `HOUR`, `DAY`, `MONTH`, or `YEAR`. See also the Time Interval and Point Unit Specifiers table. E.g., `timestampDiff(DAY, ‘2003-01-02 10:00:00’.toTimestamp, ‘2003-01-03 10:00:00’.toTimestamp)` leads to `1`.

Temporal functions	Description
`STRING.toDate`	Returns a SQL date parsed from STRING in form of “yyyy-MM-dd”.
`STRING.toTime`	Returns a SQL time parsed from STRING in form of “HH:mm:ss”.
`STRING.toTimestamp`	Returns a SQL timestamp parsed from STRING in form of “yyyy-MM-dd HH:mm:ss[.SSS]”.
`NUMERIC.year` `NUMERIC.years`	Creates an interval of months for NUMERIC years.
`NUMERIC.quarter` `NUMERIC.quarters`	Creates an interval of months for NUMERIC quarters. E.g., `2.quarters` returns 6.
`NUMERIC.month` `NUMERIC.months`	Creates an interval of NUMERIC months.
`NUMERIC.week` `NUMERIC.weeks`	Creates an interval of milliseconds for NUMERIC weeks. E.g., `2.weeks` returns 1209600000.
`NUMERIC.day` `NUMERIC.days`	Creates an interval of milliseconds for NUMERIC days.
`NUMERIC.hour` `NUMERIC.hours`	Creates an interval of milliseconds for NUMERIC hours.
`NUMERIC.minute` `NUMERIC.minutes`	Creates an interval of milliseconds for NUMERIC minutes.
`NUMERIC.second` `NUMERIC.seconds`	Creates an interval of milliseconds for NUMERIC seconds.
`NUMERIC.milli` `NUMERIC.millis`	Creates an interval of NUMERIC milliseconds.
`currentDate()`	Returns the current SQL date in the UTC time zone.
`currentTime()`	Returns the current SQL time in the UTC time zone.
`currentTimestamp()`	Returns the current SQL timestamp in the UTC time zone.
`localTime()`	Returns the current SQL time in local time zone.
`localTimestamp()`	Returns the current SQL timestamp in local time zone.
`TEMPORAL.extract(TIMEINTERVALUNIT)`	Returns a long value extracted from the TIMEINTERVALUNIT part of temporal. E.g., `“2006-06-05”.toDate.extract(TimeIntervalUnit.DAY)` returns 5; `“2006-06-05”.toDate.extract(QUARTER)` returns 2.
`TIMEPOINT.floor(TIMEINTERVALUNIT)`	Returns a value that rounds TIMEPOINT down to the time unit TIMEINTERVALUNIT. E.g., `“12:44:31”.toDate.floor(TimeIntervalUnit.MINUTE)` returns 12:44:00.
`TIMEPOINT.ceil(TIMEINTERVALUNIT)`	Returns a value that rounds TIMEPOINT up to the time unit TIMEINTERVALUNIT. E.g., `“12:44:31”.toTime.floor(TimeIntervalUnit.MINUTE)` returns 12:45:00.
`temporalOverlaps(TIMEPOINT1, TEMPORAL1, TIMEPOINT2, TEMPORAL2)`	Returns TRUE if two time intervals defined by (TIMEPOINT1, TEMPORAL1) and (TIMEPOINT2, TEMPORAL2) overlap. The temporal values could be either a time point or a time interval. E.g., `temporalOverlaps(“2:55:00”.toTime, 1.hour, “3:30:00”.toTime, 2.hour)` returns TRUE.
`dateFormat(TIMESTAMP, STRING)`	Attention This function has serious bugs and should not be used for now. Please implement a custom UDF instead or use extract() as a workaround.
`timestampDiff(TIMEPOINTUNIT, TIMEPOINT1, TIMEPOINT2)`	Returns the (signed) number of TIMEPOINTUNIT between TIMEPOINT1 and TIMEPOINT2. The unit for the interval is given by the first argument, which should be one of the following values: `SECOND`, `MINUTE`, `HOUR`, `DAY`, `MONTH`, or `YEAR`. See also the Time Interval and Point Unit Specifiers table. E.g., `timestampDiff(TimePointUnit.DAY, ‘2003-01-02 10:00:00’.toTimestamp, ‘2003-01-03 10:00:00’.toTimestamp)` leads to `1`.

Conditional Functions

Conditional functions	Description
`CASE value` `WHEN value1_1 [, value1_2 ] THEN result1` `[ WHEN value2_1 [, value2_2 ] THEN result2 ]` `[ ELSE resultZ ]` `END`	Returns resultX when the first time value is contained in (valueX_1, valueX_2, …). When no value matches, returns resultZ if it is provided and returns NULL otherwise.
`CASE` `WHEN condition1 THEN result1` `[ WHEN condition2 THEN result2 ]` `[ ELSE resultZ ]` `END`	Returns resultX when the first conditionX is met. When no condition is met, returns resultZ if it is provided and returns NULL otherwise.
`NULLIF(value1, value2)`	Returns NULL if value1 is equal to value2; returns value1 otherwise. E.g., `NULLIF(5, 5)` returns NULL; `NULLIF(5, 0)` returns 5.
`COALESCE(value1, value2 [, value3 ]* )`	Returns the first value that is not NULL from value1, value2, …. E.g., `COALESCE(NULL, 5)` returns 5.
`IF(condition, true_value, false_value)`	Returns the true_value if condition is met, otherwise false_value. Only supported in blink planner. E.g., `IF(5 > 3, 5, 3)` returns 5.
`IS_ALPHA(string)`	Returns true if all characters in string are letter, otherwise false. Only supported in blink planner.
`IS_DECIMAL(string)`	Returns true if string can be parsed to a valid numeric, otherwise false. Only supported in blink planner.
`IS_DIGIT(string)`	Returns true if all characters in string are digit, otherwise false. Only supported in blink planner.

Conditional functions Description

Conditional functions	Description
`BOOLEAN.?(VALUE1, VALUE2)`	Returns VALUE1 if BOOLEAN evaluates to TRUE; returns VALUE2 otherwise. E.g., `(42 > 5).?(‘A’, ‘B’)` returns “A”.

BOOLEAN.?(VALUE1, VALUE2)

Returns VALUE1 if BOOLEAN evaluates to TRUE; returns VALUE2 otherwise.

E.g., (42 > 5).?(‘A’, ‘B’) returns “A”.

Conditional functions Description

Conditional functions	Description
`BOOLEAN.?(VALUE1, VALUE2)`	Returns VALUE1 if BOOLEAN evaluates to TRUE; returns VALUE2 otherwise. E.g., `(42 > 5).?(“A”, “B”)` returns “A”.

BOOLEAN.?(VALUE1, VALUE2)

Returns VALUE1 if BOOLEAN evaluates to TRUE; returns VALUE2 otherwise.

E.g., (42 > 5).?(“A”, “B”) returns “A”.

Type Conversion Functions

Type conversion functions Description

Type conversion functions	Description
`CAST(value AS type)`	Returns a new value being cast to type type. See the supported types here. E.g., `CAST(‘42’ AS INT)` returns 42; `CAST(NULL AS VARCHAR)` returns NULL of type VARCHAR.

CAST(value AS type)

Returns a new value being cast to type type. See the supported types here.

E.g., CAST(‘42’ AS INT) returns 42; CAST(NULL AS VARCHAR) returns NULL of type VARCHAR.

Type conversion functions Description

Type conversion functions	Description
`ANY.cast(TYPE)`	Returns a new ANY being cast to type TYPE. See the supported types here. E.g., `‘42’.cast(INT)` returns 42; `Null(STRING)` returns NULL of type STRING.

ANY.cast(TYPE)

Returns a new ANY being cast to type TYPE. See the supported types here.

E.g., ‘42’.cast(INT) returns 42; Null(STRING) returns NULL of type STRING.

Type conversion functions Description

Type conversion functions	Description
`ANY.cast(TYPE)`	Returns a new ANY being cast to type TYPE. See the supported types here. E.g., `“42”.cast(Types.INT)` returns 42; `Null(Types.STRING)` returns NULL of type STRING.

ANY.cast(TYPE)

Returns a new ANY being cast to type TYPE. See the supported types here.

E.g., “42”.cast(Types.INT) returns 42; Null(Types.STRING) returns NULL of type STRING.

Collection Functions

Collection functions	Description
`CARDINALITY(array)`	Returns the number of elements in array.
`array ‘[’ integer ‘]’`	Returns the element at position integer in array. The index starts from 1.
`ELEMENT(array)`	Returns the sole element of array (whose cardinality should be one); returns NULL if array is empty. Throws an exception if array has more than one element.
`CARDINALITY(map)`	Returns the number of entries in map.
`map ‘[’ value ‘]’`	Returns the value specified by key value in map.

Collection functions	Description
`ARRAY.cardinality()`	Returns the number of elements in ARRAY.
`ARRAY.at(INT)`	Returns the element at position INT in ARRAY. The index starts from 1.
`ARRAY.element()`	Returns the sole element of ARRAY (whose cardinality should be one); returns NULL if ARRAY is empty. Throws an exception if ARRAY has more than one element.
`MAP.cardinality()`	Returns the number of entries in MAP.
`MAP.at(ANY)`	Returns the value specified by key ANY in MAP.

Collection functions	Description
`ARRAY.cardinality()`	Returns the number of elements in ARRAY.
`ARRAY.at(INT)`	Returns the element at position INT in ARRAY. The index starts from 1.
`ARRAY.element()`	Returns the sole element of ARRAY (whose cardinality should be one); returns NULL if ARRAY is empty. Throws an exception if ARRAY has more than one element.
`MAP.cardinality()`	Returns the number of entries in MAP.
`MAP.at(ANY)`	Returns the value specified by key ANY in MAP.

Value Construction Functions

Value construction functions	Description
`ROW(value1, [, value2])` `(value1, [, value2])`	Returns a row created from a list of values (value1, value2,…).
`ARRAY ‘[’ value1 [, value2 ] ‘]’`	Returns an array created from a list of values (value1, value2, …).
`MAP ‘[’ value1, value2 [, value3, value4 ] ‘]’`	Returns a map created from a list of key-value pairs ((value1, value2), (value3, value4), …).

Value constructor functions	Description
`row(ANY1, ANY2, …)`	Returns a row created from a list of object values (ANY1, ANY2, …). Row is composite type that can be access via value access functions.
`array(ANY1, ANY2, …)`	Returns an array created from a list of object values (ANY1, ANY2, …).
`map(ANY1, ANY2, ANY3, ANY4, …)`	Returns a map created from a list of key-value pairs ((ANY1, ANY2), (ANY3, ANY4), …).
`NUMERIC.rows`	Creates a NUMERIC interval of rows (commonly used in window creation).

Value constructor functions	Description
`row(ANY1, ANY2, …)`	Returns a row created from a list of object values (ANY1, ANY2, …). Row is composite type that can be access via value access functions.
`array(ANY1, ANY2, …)`	Returns an array created from a list of object values (ANY1, ANY2, …).
`map(ANY1, ANY2, ANY3, ANY4, …)`	Returns a map created from a list of key-value pairs ((ANY1, ANY2), (ANY3, ANY4), …).
`NUMERIC.rows`	Creates a NUMERIC interval of rows (commonly used in window creation).

Value Access Functions

Value access functions	Description
`tableName.compositeType.field`	Returns the value of a field from a Flink composite type (e.g., Tuple, POJO) by name.
`tableName.compositeType.*`	Returns a flat representation of a Flink composite type (e.g., Tuple, POJO) that converts each of its direct subtype into a separate field. In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g., `mypojo$mytuple$f0`).

Value access functions Description

Value access functions	Description
`COMPOSITE.get(STRING)` `COMPOSITE.get(INT)`	Returns the value of a field from a Flink composite type (e.g., Tuple, POJO) by name or index. E.g., `pojo.get(‘myField’)` or `tuple.get(0)`.
`ANY.flatten()`	Returns a flat representation of a Flink composite type (e.g., Tuple, POJO) that converts each of its direct subtype into a separate field. In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g., `mypojo$mytuple$f0`).

COMPOSITE.get(STRING)
COMPOSITE.get(INT)

Returns the value of a field from a Flink composite type (e.g., Tuple, POJO) by name or index.

E.g., pojo.get(‘myField’) or tuple.get(0).

ANY.flatten()

Returns a flat representation of a Flink composite type (e.g., Tuple, POJO) that converts each of its direct subtype into a separate field. In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g., mypojo$mytuple$f0).

Value access functions Description

Value access functions	Description
`COMPOSITE.get(STRING)` `COMPOSITE.get(INT)`	Returns the value of a field from a Flink composite type (e.g., Tuple, POJO) by name or index. E.g., `‘pojo.get(“myField”)` or `‘tuple.get(0)`.
`ANY.flatten()`	Returns a flat representation of a Flink composite type (e.g., Tuple, POJO) that converts each of its direct subtype into a separate field. In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g., `mypojo$mytuple$f0`).

COMPOSITE.get(STRING)
COMPOSITE.get(INT)

Returns the value of a field from a Flink composite type (e.g., Tuple, POJO) by name or index.

E.g., ‘pojo.get(“myField”) or ‘tuple.get(0).

ANY.flatten()

Grouping Functions

Grouping functions	Description
`GROUP_ID()`	Returns an integer that uniquely identifies the combination of grouping keys.
`GROUPING(expression1 [, expression2] )` `GROUPING_ID(expression1 [, expression2] )`	Returns a bit vector of the given grouping expressions.

Grouping functions	Description

Grouping functions	Description

Hash Functions

Hash functions	Description
`MD5(string)`	Returns the MD5 hash of string as a string of 32 hexadecimal digits; returns NULL if string is NULL.
`SHA1(string)`	Returns the SHA-1 hash of string as a string of 40 hexadecimal digits; returns NULL if string is NULL.
`SHA224(string)`	Returns the SHA-224 hash of string as a string of 56 hexadecimal digits; returns NULL if string is NULL.
`SHA256(string)`	Returns the SHA-256 hash of string as a string of 64 hexadecimal digits; returns NULL if string is NULL.
`SHA384(string)`	Returns the SHA-384 hash of string as a string of 96 hexadecimal digits; returns NULL if string is NULL.
`SHA512(string)`	Returns the SHA-512 hash of string as a string of 128 hexadecimal digits; returns NULL if string is NULL.
`SHA2(string, hashLength)`	Returns the hash using the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, or SHA-512). The first argument string is the string to be hashed and the second argument hashLength is the bit length of the result (224, 256, 384, or 512). Returns NULL if string or hashLength is NULL.

Hash functions	Description
`STRING.md5()`	Returns the MD5 hash of STRING as a string of 32 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha1()`	Returns the SHA-1 hash of STRING as a string of 40 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha224()`	Returns the SHA-224 hash of STRING as a string of 56 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha256()`	Returns the SHA-256 hash of STRING as a string of 64 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha384()`	Returns the SHA-384 hash of STRING as a string of 96 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha512()`	Returns the SHA-512 hash of STRING as a string of 128 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha2(INT)`	Returns the SHA-2 family (SHA-224, SHA-256, SHA-384, or SHA-512) hashed value specified by INT (which could be 224, 256, 384, or 512) for STRING. Returns NULL if STRING or INT is NULL.

Hash functions	Description
`STRING.md5()`	Returns the MD5 hash of STRING as a string of 32 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha1()`	Returns the SHA-1 hash of STRING as a string of 40 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha224()`	Returns the SHA-224 hash of STRING as a string of 56 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha256()`	Returns the SHA-256 hash of STRING as a string of 64 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha384()`	Returns the SHA-384 hash of STRING as a string of 96 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha512()`	Returns the SHA-512 hash of STRING as a string of 128 hexadecimal digits; returns NULL if STRING is NULL.
`STRING.sha2(INT)`	Returns the SHA-2 family (SHA-224, SHA-256, SHA-384, or SHA-512) hashed value specified by INT (which could be 224, 256, 384, or 512) for STRING. Returns NULL if STRING or INT is NULL.

Auxiliary Functions

Auxiliary functions	Description

Auxiliary functions	Description
`ANY.as(NAME1, NAME2, …)`	Specifies a name for ANY (a field). Additional names can be specified if the expression expands to multiple fields.

Auxiliary functions	Description
`ANY.as(NAME1, NAME2, …)`	Specifies a name for ANY (a field). Additional names can be specified if the expression expands to multiple fields.

Aggregate Functions

The aggregate functions take an expression across all the rows as the input and return a single aggregated value as the result.

Aggregate functions	Description
`COUNT([ ALL ] expression \| DISTINCT expression1 [, expression2])`	By default or with ALL, returns the number of input rows for which expression is not NULL. Use DISTINCT for one unique instance of each value.
`COUNT()` `COUNT(1)`	Returns the number of input rows.
`AVG([ ALL \| DISTINCT ] expression)`	By default or with keyword ALL, returns the average (arithmetic mean) of expression across all input rows. Use DISTINCT for one unique instance of each value.
`SUM([ ALL \| DISTINCT ] expression)`	By default or with keyword ALL, returns the sum of expression across all input rows. Use DISTINCT for one unique instance of each value.
`MAX([ ALL \| DISTINCT ] expression)`	By default or with keyword ALL, returns the maximum value of expression across all input rows. Use DISTINCT for one unique instance of each value.
`MIN([ ALL \| DISTINCT ] expression)`	By default or with keyword ALL, returns the minimum value of expression across all input rows. Use DISTINCT for one unique instance of each value.
`STDDEV_POP([ ALL \| DISTINCT ] expression)`	By default or with keyword ALL, returns the population standard deviation of expression across all input rows. Use DISTINCT for one unique instance of each value.
`STDDEV_SAMP([ ALL \| DISTINCT ] expression)`	By default or with keyword ALL, returns the sample standard deviation of expression across all input rows. Use DISTINCT for one unique instance of each value.
`VAR_POP([ ALL \| DISTINCT ] expression)`	By default or with keyword ALL, returns the population variance (square of the population standard deviation) of expression across all input rows. Use DISTINCT for one unique instance of each value.
`VAR_SAMP([ ALL \| DISTINCT ] expression)`	By default or with keyword ALL, returns the sample variance (square of the sample standard deviation) of expression across all input rows. Use DISTINCT for one unique instance of each value.
`COLLECT([ ALL \| DISTINCT ] expression)`	By default or with keyword ALL, returns a multiset of expression across all input rows. NULL values will be ignored. Use DISTINCT for one unique instance of each value.
`VARIANCE([ ALL \| DISTINCT ] expression)`	Synonyms for VAR_SAMP(). Only supported in blink planner.
`RANK()`	Returns the rank of a value in a group of values. The result is one plus the number of rows preceding or equal to the current row in the ordering of the partition. The values will produce gaps in the sequence. Only supported in blink planner.
`DENSE_RANK()`	Returns the rank of a value in a group of values. The result is one plus the previously assigned rank value. Unlike the function rank, dense_rank will not produce gaps in the ranking sequence. Only supported in blink planner.
`ROW_NUMBER()`	Assigns a unique, sequential number to each row, starting with one, according to the ordering of rows within the window partition. ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5). Only supported in blink planner.
`LEAD(expression [, offset] [, default] )`	Returns the value of expression at the offsetth row after the current row in the window. The default value of offset is 1 and the default value of default is NULL. Only supported in blink planner.
`LAG(expression [, offset] [, default])`	Returns the value of expression at the offsetth row after the current row in the window. The default value of offset is 1 and the default value of default is NULL. Only supported in blink planner.
`FIRST_VALUE(expression)`	Returns the first value in an ordered set of values. Only supported in blink planner.
`LAST_VALUE(expression)`	Returns the last value in an ordered set of values. Only supported in blink planner.
`LISTAGG(expression [, separator])`	Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string. The default value of separator is ‘,’. Only supported in blink planner.

Aggregate functions	Description
`FIELD.count`	Returns the number of input rows for which FIELD is not NULL.
`FIELD.avg`	Returns the average (arithmetic mean) of FIELD across all input rows.
`FIELD.sum`	Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns NULL.
`FIELD.sum0`	Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns 0.
`FIELD.max`	Returns the maximum value of numeric field FIELD across all input rows.
`FIELD.min`	Returns the minimum value of numeric field FIELD across all input rows.
`FIELD.stddevPop`	Returns the population standard deviation of numeric field FIELD across all input rows.
`FIELD.stddevSamp`	Returns the sample standard deviation of numeric field FIELD across all input rows.
`FIELD.varPop`	Returns the population variance (square of the population standard deviation) of numeric field FIELD across all input rows.
`FIELD.varSamp`	Returns the sample variance (square of the sample standard deviation) of numeric field FIELD across all input rows.
`FIELD.collect`	Returns a multiset of FIELD across all input rows.

Aggregate functions	Description
`FIELD.count`	Returns the number of input rows for which FIELD is not NULL.
`FIELD.avg`	Returns the average (arithmetic mean) of FIELD across all input rows.
`FIELD.sum`	Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns NULL.
`FIELD.sum0`	Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns 0.
`FIELD.max`	Returns the maximum value of numeric field FIELD across all input rows.
`FIELD.min`	Returns the minimum value of numeric field FIELD across all input rows.
`FIELD.stddevPop`	Returns the population standard deviation of numeric field FIELD across all input rows.
`FIELD.stddevSamp`	Returns the sample standard deviation of numeric field FIELD across all input rows.
`FIELD.varPop`	Returns the population variance (square of the population standard deviation) of numeric field FIELD across all input rows.
`FIELD.varSamp`	Returns the sample variance (square of the sample standard deviation) of numeric field FIELD across all input rows.
`FIELD.collect`	Returns a multiset of FIELD across all input rows.

Time Interval and Point Unit Specifiers

The following table lists specifiers for time interval and time point units.

For Table API, please use _ for spaces (e.g., DAY_TO_HOUR).

Time Interval Unit	Time Point Unit
`MILLENIUM` (SQL-only)
`CENTURY` (SQL-only)
`YEAR`	`YEAR`
`YEAR TO MONTH`
`QUARTER`	`QUARTER`
`MONTH`	`MONTH`
`WEEK`	`WEEK`
`DAY`	`DAY`
`DAY TO HOUR`
`DAY TO MINUTE`
`DAY TO SECOND`
`HOUR`	`HOUR`
`HOUR TO MINUTE`
`HOUR TO SECOND`
`MINUTE`	`MINUTE`
`MINUTE TO SECOND`
`SECOND`	`SECOND`
	`MILLISECOND`
	`MICROSECOND`
`DOY` (SQL-only)
`DOW` (SQL-only)
	`SQL_TSI_YEAR` (SQL-only)
	`SQL_TSI_QUARTER` (SQL-only)
	`SQL_TSI_MONTH` (SQL-only)
	`SQL_TSI_WEEK` (SQL-only)
	`SQL_TSI_DAY` (SQL-only)
	`SQL_TSI_HOUR` (SQL-only)
	`SQL_TSI_MINUTE` (SQL-only)
	`SQL_TSI_SECOND` (SQL-only)

Column Functions

The column functions are used to select or deselect table columns.

SYNTAX	DESC
withColumns(…)	select the specified columns
withoutColumns(…)	deselect the columns specified

The detailed syntax is as follows:

columnFunction:
    withColumns(columnExprs)
    withoutColumns(columnExprs)
columnExprs:
    columnExpr [, columnExpr]*
columnExpr:
    columnRef | columnIndex to columnIndex | columnName to columnName
columnRef:
    columnName(The field name that exists in the table) | columnIndex(a positive integer starting from 1)

The usage of the column function is illustrated in the following table. (Suppose we have a table with 5 columns: (a: Int, b: Long, c: String, d:String, e: String)):

Api	Usage	Description
withColumns()\|	`select(“withColumns()”) \| select(““) = select(“a, b, c, d, e”)`	all the columns
withColumns(m to n)	`select(“withColumns(2 to 4)”) = select(“b, c, d”)`	columns from m to n
withColumns(m, n, k)	`select(“withColumns(1, 3, e)”) = select(“a, c, e”)`	columns m, n, k
withColumns(m, n to k)	`select(“withColumns(1, 3 to 5)”) = select(“a, c, d ,e”)`	mixing of the above two representation
withoutColumns(m to n)	`select(“withoutColumns(2 to 4)”) = select(“a, e”)`	deselect columns from m to n
withoutColumns(m, n, k)	`select(“withoutColumns(1, 3, 5)”) = select(“b, d”)`	deselect columns m, n, k
withoutColumns(m, n to k)	`select(“withoutColumns(1, 3 to 5)”) = select(“b”)`	mixing of the above two representation

Api	Usage	Description
withColumns()\|	`select(withColumns(‘)) \| select(‘) = select(‘a, ‘b, ‘c, ‘d, ‘e)`	all the columns
withColumns(m to n)	`select(withColumns(2 to 4)) = select(‘b, ‘c, ‘d)`	columns from m to n
withColumns(m, n, k)	`select(withColumns(1, 3, ‘e)) = select(‘a, ‘c, ‘e)`	columns m, n, k
withColumns(m, n to k)	`select(withColumns(1, 3 to 5)) = select(‘a, ‘c, ‘d, ‘e)`	mixing of the above two representation
withoutColumns(m to n)	`select(withoutColumns(2 to 4)) = select(‘a, ‘e)`	deselect columns from m to n
withoutColumns(m, n, k)	`select(withoutColumns(1, 3, 5)) = select(‘b, ‘d)`	deselect columns m, n, k
withoutColumns(m, n to k)	`select(withoutColumns(1, 3 to 5)) = select(‘b)`	mixing of the above two representation

The column functions can be used in all places where column fields are expected, such as select, groupBy, orderBy, UDFs etc. e.g.:

table
   .groupBy("withColumns(1 to 3)")
   .select("withColumns(a to b), myUDAgg(myUDF(withColumns(5 to 20)))")

table
   .groupBy(withColumns(1 to 3))
   .select(withColumns('a to 'b), myUDAgg(myUDF(withColumns(5 to 20))))

table \
    .group_by("withColumns(1 to 3)") \
    .select("withColumns(a to b), myUDAgg(myUDF(withColumns(5 to 20)))")

Note Column functions are only used in Table API.