# 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](udfs.html). If you think that the function is general enough, please [open a Jira issue](https://issues.apache.org/jira/secure/CreateIssue!default.jspa) 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 | | --- | --- | | <figure class="highlight"> ``` value1 = value2 ``` </figure> | Returns TRUE if _value1_ is equal to _value2_; returns UNKNOWN if _value1_ or _value2_ is NULL. | | <figure class="highlight"> ``` value1 <> value2 ``` </figure> | Returns TRUE if _value1_ is not equal to _value2_; returns UNKNOWN if _value1_ or _value2_ is NULL. | | <figure class="highlight"> ``` value1 > value2 ``` </figure> | Returns TRUE if _value1_ is greater than _value2_; returns UNKNOWN if _value1_ or _value2_ is NULL. | | <figure class="highlight"> ``` value1 >= value2 ``` </figure> | Returns TRUE if _value1_ is greater than or equal to _value2_; returns UNKNOWN if _value1_ or _value2_ is NULL. | | <figure class="highlight"> ``` value1 < value2 ``` </figure> | Returns TRUE if _value1_ is less than _value2_; returns UNKNOWN if _value1_ or _value2_ is NULL. | | <figure class="highlight"> ``` value1 <= value2 ``` </figure> | Returns TRUE if _value1_ is less than or equal to _value2_; returns UNKNOWN if _value1_ or _value2_ is NULL. | | <figure class="highlight"> ``` value IS NULL ``` </figure> | Returns TRUE if _value_ is NULL. | | <figure class="highlight"> ``` value IS NOT NULL ``` </figure> | Returns TRUE if _value_ is not NULL. | | <figure class="highlight"> ``` value1 IS DISTINCT FROM value2 ``` </figure> | 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. | | <figure class="highlight"> ``` value1 IS NOT DISTINCT FROM value2 ``` </figure> | 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. | | <figure class="highlight"> ``` value1 BETWEEN [ ASYMMETRIC | SYMMETRIC ] value2 AND value3 ``` </figure> | 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. | | <figure class="highlight"> ``` value1 NOT BETWEEN [ ASYMMETRIC | SYMMETRIC ] value2 AND value3 ``` </figure> | 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. | | <figure class="highlight"> ``` string1 LIKE string2 [ ESCAPE char ] ``` </figure> | 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. | | <figure class="highlight"> ``` string1 NOT LIKE string2 [ ESCAPE char ] ``` </figure> | 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. | | <figure class="highlight"> ``` string1 SIMILAR TO string2 [ ESCAPE char ] ``` </figure> | 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. | | <figure class="highlight"> ``` string1 NOT SIMILAR TO string2 [ ESCAPE char ] ``` </figure> | 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. | | <figure class="highlight"> ``` value1 IN (value2 [, value3]* ) ``` </figure> | 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. | | <figure class="highlight"> ``` value1 NOT IN (value2 [, value3]* ) ``` </figure> | 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. | | <figure class="highlight"> ``` EXISTS (sub-query) ``` </figure> | 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](streaming/query_configuration.html) for details. | | <figure class="highlight"> ``` value IN (sub-query) ``` </figure> | 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](streaming/query_configuration.html) for details. | | <figure class="highlight"> ``` value NOT IN (sub-query) ``` </figure> | 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](streaming/query_configuration.html) for details. | | Comparison functions | Description | | --- | --- | | <figure class="highlight"> ``` ANY1 === ANY2 ``` </figure> | Returns TRUE if _ANY1_ is equal to _ANY2_; returns UNKNOWN if _ANY1_ or _ANY2_ is NULL. | | <figure class="highlight"> ``` ANY1 !== ANY2 ``` </figure> | Returns TRUE if _ANY1_ is not equal to _ANY2_; returns UNKNOWN if _ANY1_ or _ANY2_ is NULL. | | <figure class="highlight"> ``` ANY1 > ANY2 ``` </figure> | Returns TRUE if _ANY1_ is greater than _ANY2_; returns UNKNOWN if _ANY1_ or _ANY2_ is NULL. | | <figure class="highlight"> ``` ANY1 >= ANY2 ``` </figure> | Returns TRUE if _ANY1_ is greater than or equal to _ANY2_; returns UNKNOWN if _ANY1_ or _ANY2_ is NULL. | | <figure class="highlight"> ``` ANY1 < ANY2 ``` </figure> | Returns TRUE if _ANY1_ is less than _ANY2_; returns UNKNOWN if _ANY1_ or _ANY2_ is NULL. | | <figure class="highlight"> ``` ANY1 <= ANY2 ``` </figure> | Returns TRUE if _ANY1_ is less than or equal to _ANY2_; returns UNKNOWN if _ANY1_ or _ANY2_ is NULL. | | <figure class="highlight"> ``` ANY.isNull ``` </figure> | Returns TRUE if _ANY_ is NULL. | | <figure class="highlight"> ``` ANY.isNotNull ``` </figure> | Returns TRUE if _ANY_ is not NULL. | | <figure class="highlight"> ``` STRING1.like(STRING2) ``` </figure> | Returns TRUE if _STRING1_ matches pattern _STRING2_; returns UNKNOWN if _STRING1_ or _STRING2_ is NULL.E.g., `"JoKn".like("Jo_n%")` returns TRUE. | | <figure class="highlight"> ``` STRING.similar(STRING) ``` </figure> | Returns TRUE if _STRING1_ matches SQL regular expression _STRING2_; returns UNKNOWN if _STRING1_ or _STRING2_ is NULL.E.g., `"A".similar("A+")` returns TRUE. | | <figure class="highlight"> ``` ANY1.in(ANY2, ANY3, ...) ``` </figure> | 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. | | <figure class="highlight"> ``` ANY.in(TABLE) ``` </figure> | 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](streaming/query_configuration.html) for details. | | <figure class="highlight"> ``` ANY1.between(ANY2, ANY3) ``` </figure> | 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. | | <figure class="highlight"> ``` ANY1.notBetween(ANY2, ANY3) ``` </figure> | 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 | | --- | --- | | <figure class="highlight"> ``` ANY1 === ANY2 ``` </figure> | Returns TRUE if _ANY1_ is equal to _ANY2_; returns UNKNOWN if _ANY1_ or _ANY2_ is NULL. | | <figure class="highlight"> ``` ANY1 !== ANY2 ``` </figure> | Returns TRUE if _ANY1_ is not equal to _ANY2_; returns UNKNOWN if _ANY1_ or _ANY2_ is NULL. | | <figure class="highlight"> ``` ANY1 > ANY2 ``` </figure> | Returns TRUE if _ANY1_ is greater than _ANY2_; returns UNKNOWN if _ANY1_ or _ANY2_ is NULL. | | <figure class="highlight"> ``` ANY1 >= ANY2 ``` </figure> | Returns TRUE if _ANY1_ is greater than or equal to _ANY2_; returns UNKNOWN if _ANY1_ or _ANY2_ is NULL. | | <figure class="highlight"> ``` ANY1 < ANY2 ``` </figure> | Returns TRUE if _ANY1_ is less than _ANY2_; returns UNKNOWN if _ANY1_ or _ANY2_ is NULL. | | <figure class="highlight"> ``` ANY1 <= ANY2 ``` </figure> | Returns TRUE if _ANY1_ is less than or equal to _ANY2_; returns UNKNOWN if _ANY1_ or _ANY2_ is NULL. | | <figure class="highlight"> ``` ANY.isNull ``` </figure> | Returns TRUE if _ANY_ is NULL. | | <figure class="highlight"> ``` ANY.isNotNull ``` </figure> | Returns TRUE if _ANY_ is not NULL. | | <figure class="highlight"> ``` STRING1.like(STRING2) ``` </figure> | Returns TRUE if _STRING1_ matches pattern _STRING2_; returns UNKNOWN if _STRING1_ or _STRING2_ is NULL.E.g., `"JoKn".like("Jo_n%")` returns TRUE. | | <figure class="highlight"> ``` STRING1.similar(STRING2) ``` </figure> | Returns TRUE if _STRING1_ matches SQL regular expression _STRING2_; returns UNKNOWN if _STRING1_ or _STRING2_ is NULL.E.g., `"A".similar("A+")` returns TRUE. | | <figure class="highlight"> ``` ANY1.in(ANY2, ANY3, ...) ``` </figure> | 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. | | <figure class="highlight"> ``` ANY.in(TABLE) ``` </figure> | 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](streaming/query_configuration.html) for details. | | <figure class="highlight"> ``` ANY1.between(ANY2, ANY3) ``` </figure> | 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. | | <figure class="highlight"> ``` ANY1.notBetween(ANY2, ANY3) ``` </figure> | 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 | | --- | --- | | <figure class="highlight"> ``` boolean1 OR boolean2 ``` </figure> | Returns TRUE if _boolean1_ is TRUE or _boolean2_ is TRUE. Supports three-valued logic.E.g., `TRUE OR UNKNOWN` returns TRUE. | | <figure class="highlight"> ``` boolean1 AND boolean2 ``` </figure> | Returns TRUE if _boolean1_ and _boolean2_ are both TRUE. Supports three-valued logic.E.g., `TRUE AND UNKNOWN` returns UNKNOWN. | | <figure class="highlight"> ``` NOT boolean ``` </figure> | Returns TRUE if _boolean_ is FALSE; returns FALSE if _boolean_ is TRUE; returns UNKNOWN if _boolean_ is UNKNOWN. | | <figure class="highlight"> ``` boolean IS FALSE ``` </figure> | Returns TRUE if _boolean_ is FALSE; returns FALSE if _boolean_ is TRUE or UNKNOWN. | | <figure class="highlight"> ``` boolean IS NOT FALSE ``` </figure> | Returns TRUE if _boolean_ is TRUE or UNKNOWN; returns FALSE if _boolean_ is FALSE. | | <figure class="highlight"> ``` boolean IS TRUE ``` </figure> | Returns TRUE if _boolean_ is TRUE; returns FALSE if _boolean_ is FALSE or UNKNOWN. | | <figure class="highlight"> ``` boolean IS NOT TRUE ``` </figure> | Returns TRUE if _boolean_ is FALSE or UNKNOWN; returns FALSE if _boolean_ is FALSE. | | <figure class="highlight"> ``` boolean IS UNKNOWN ``` </figure> | Returns TRUE if _boolean_ is UNKNOWN; returns FALSE if _boolean_ is TRUE or FALSE. | | <figure class="highlight"> ``` boolean IS NOT UNKNOWN ``` </figure> | Returns TRUE if _boolean_ is TRUE or FALSE; returns FALSE if _boolean_ is UNKNOWN. | | Logical functions | Description | | --- | --- | | <figure class="highlight"> ``` BOOLEAN1 || BOOLEAN2 ``` </figure> | Returns TRUE if _BOOLEAN1_ is TRUE or _BOOLEAN2_ is TRUE. Supports three-valued logic.E.g., `true || Null(BOOLEAN)` returns TRUE. | | <figure class="highlight"> ``` BOOLEAN1 && BOOLEAN2 ``` </figure> | Returns TRUE if _BOOLEAN1_ and _BOOLEAN2_ are both TRUE. Supports three-valued logic.E.g., `true && Null(BOOLEAN)` returns UNKNOWN. | | <figure class="highlight"> ``` !BOOLEAN ``` </figure> | Returns TRUE if _BOOLEAN_ is FALSE; returns FALSE if _BOOLEAN_ is TRUE; returns UNKNOWN if _BOOLEAN_ is UNKNOWN. | | <figure class="highlight"> ``` BOOLEAN.isTrue ``` </figure> | Returns TRUE if _BOOLEAN_ is TRUE; returns FALSE if _BOOLEAN_ is FALSE or UNKNOWN. | | <figure class="highlight"> ``` BOOLEAN.isFalse ``` </figure> | Returns TRUE if _BOOLEAN_ is FALSE; returns FALSE if _BOOLEAN_ is TRUE or UNKNOWN. | | <figure class="highlight"> ``` BOOLEAN.isNotTrue ``` </figure> | Returns TRUE if _BOOLEAN_ is FALSE or UNKNOWN; returns FALSE if _BOOLEAN_ is FALSE. | | <figure class="highlight"> ``` BOOLEAN.isNotFalse ``` </figure> | Returns TRUE if _BOOLEAN_ is TRUE or UNKNOWN; returns FALSE if _BOOLEAN_ is FALSE. | | Logical functions | Description | | --- | --- | | <figure class="highlight"> ``` BOOLEAN1 || BOOLEAN2 ``` </figure> | Returns TRUE if _BOOLEAN1_ is TRUE or _BOOLEAN2_ is TRUE. Supports three-valued logic.E.g., `true || Null(Types.BOOLEAN)` returns TRUE. | | <figure class="highlight"> ``` BOOLEAN1 && BOOLEAN2 ``` </figure> | Returns TRUE if _BOOLEAN1_ and _BOOLEAN2_ are both TRUE. Supports three-valued logic.E.g., `true && Null(Types.BOOLEAN)` returns UNKNOWN. | | <figure class="highlight"> ``` !BOOLEAN ``` </figure> | Returns TRUE if _BOOLEAN_ is FALSE; returns FALSE if _BOOLEAN_ is TRUE; returns UNKNOWN if _BOOLEAN_ is UNKNOWN. | | <figure class="highlight"> ``` BOOLEAN.isTrue ``` </figure> | Returns TRUE if _BOOLEAN_ is TRUE; returns FALSE if _BOOLEAN_ is FALSE or UNKNOWN. | | <figure class="highlight"> ``` BOOLEAN.isFalse ``` </figure> | Returns TRUE if _BOOLEAN_ is FALSE; returns FALSE if _BOOLEAN_ is TRUE or UNKNOWN. | | <figure class="highlight"> ``` BOOLEAN.isNotTrue ``` </figure> | Returns TRUE if _BOOLEAN_ is FALSE or UNKNOWN; returns FALSE if _BOOLEAN_ is FALSE. | | <figure class="highlight"> ``` BOOLEAN.isNotFalse ``` </figure> | Returns TRUE if _BOOLEAN_ is TRUE or UNKNOWN; returns FALSE if _BOOLEAN_ is FALSE. | ### Arithmetic Functions | Arithmetic functions | Description | | --- | --- | | <figure class="highlight"> ``` + numeric ``` </figure> | Returns _numeric_. | | <figure class="highlight"> ``` - numeric ``` </figure> | Returns negative _numeric_. | | <figure class="highlight"> ``` numeric1 + numeric2 ``` </figure> | Returns _numeric1_ plus _numeric2_. | | <figure class="highlight"> ``` numeric1 - numeric2 ``` </figure> | Returns _numeric1_ minus _numeric2_. | | <figure class="highlight"> ``` numeric1 * numeric2 ``` </figure> | Returns _numeric1_ multiplied by _numeric2_. | | <figure class="highlight"> ``` numeric1 / numeric2 ``` </figure> | Returns _numeric1_ divided by _numeric2_. | | <figure class="highlight"> ``` POWER(numeric1, numeric2) ``` </figure> | Returns _numeric1_ raised to the power of _numeric2_. | | <figure class="highlight"> ``` ABS(numeric) ``` </figure> | Returns the absolute value of _numeric_. | | <figure class="highlight"> ``` MOD(numeric1, numeric2) ``` </figure> | Returns the remainder (modulus) of _numeric1_ divided by _numeric2_. The result is negative only if _numeric1_ is negative. | | <figure class="highlight"> ``` SQRT(numeric) ``` </figure> | Returns the square root of _numeric_. | | <figure class="highlight"> ``` LN(numeric) ``` </figure> | Returns the natural logarithm (base e) of _numeric_. | | <figure class="highlight"> ``` LOG10(numeric) ``` </figure> | Returns the base 10 logarithm of _numeric_. | | <figure class="highlight"> ``` LOG2(numeric) ``` </figure> | Returns the base 2 logarithm of _numeric_. | | <figure class="highlight"> ``` LOG(numeric2) LOG(numeric1, numeric2) ``` </figure> | 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. | | <figure class="highlight"> ``` EXP(numeric) ``` </figure> | Returns e raised to the power of _numeric_. | | <figure class="highlight"> ``` CEIL(numeric) CEILING(numeric) ``` </figure> | Rounds _numeric_ up, and returns the smallest number that is greater than or equal to _numeric_. | | <figure class="highlight"> ``` FLOOR(numeric) ``` </figure> | Rounds _numeric_ down, and returns the largest number that is less than or equal to _numeric_. | | <figure class="highlight"> ``` SIN(numeric) ``` </figure> | Returns the sine of _numeric_. | | <figure class="highlight"> ``` SINH(numeric) ``` </figure> | Returns the hyperbolic sine of _numeric_.The return type is _DOUBLE_. | | <figure class="highlight"> ``` COS(numeric) ``` </figure> | Returns the cosine of _numeric_. | | <figure class="highlight"> ``` TAN(numeric) ``` </figure> | Returns the tangent of _numeric_. | | <figure class="highlight"> ``` TANH(numeric) ``` </figure> | Returns the hyperbolic tangent of _numeric_.The return type is _DOUBLE_. | | <figure class="highlight"> ``` COT(numeric) ``` </figure> | Returns the cotangent of a _numeric_. | | <figure class="highlight"> ``` ASIN(numeric) ``` </figure> | Returns the arc sine of _numeric_. | | <figure class="highlight"> ``` ACOS(numeric) ``` </figure> | Returns the arc cosine of _numeric_. | | <figure class="highlight"> ``` ATAN(numeric) ``` </figure> | Returns the arc tangent of _numeric_. | | <figure class="highlight"> ``` ATAN2(numeric1, numeric2) ``` </figure> | Returns the arc tangent of a coordinate _(numeric1, numeric2)_. | | <figure class="highlight"> ``` COSH(numeric) ``` </figure> | Returns the hyperbolic cosine of _NUMERIC_.Return value type is _DOUBLE_. | | <figure class="highlight"> ``` DEGREES(numeric) ``` </figure> | Returns the degree representation of a radian _numeric_. | | <figure class="highlight"> ``` RADIANS(numeric) ``` </figure> | Returns the radian representation of a degree _numeric_. | | <figure class="highlight"> ``` SIGN(numeric) ``` </figure> | Returns the signum of _numeric_. | | <figure class="highlight"> ``` ROUND(numeric, integer) ``` </figure> | Returns a number rounded to _integer_ decimal places for _numeric_. | | <figure class="highlight"> ``` PI ``` </figure> | Returns a value that is closer than any other values to pi. | | <figure class="highlight"> ``` E() ``` </figure> | Returns a value that is closer than any other values to e. | | <figure class="highlight"> ``` RAND() ``` </figure> | Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive). | | <figure class="highlight"> ``` RAND(integer) ``` </figure> | 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. | | <figure class="highlight"> ``` RAND_INTEGER(integer) ``` </figure> | Returns a pseudorandom integer value between 0 (inclusive) and _integer_ (exclusive). | | <figure class="highlight"> ``` RAND_INTEGER(integer1, integer2) ``` </figure> | 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. | | <figure class="highlight"> ``` UUID() ``` </figure> | 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. | | <figure class="highlight"> ``` BIN(integer) ``` </figure> | 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'. | | <figure class="highlight"> ``` HEX(numeric) HEX(string) ``` </figure> | 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". | | Arithmetic functions | Description | | --- | --- | | <figure class="highlight"> ``` + NUMERIC ``` </figure> | Returns _NUMERIC_. | | <figure class="highlight"> ``` - NUMERIC ``` </figure> | Returns negative _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC1 + NUMERIC2 ``` </figure> | Returns _NUMERIC1_ plus _NUMERIC2_. | | <figure class="highlight"> ``` NUMERIC1 - NUMERIC2 ``` </figure> | Returns _NUMERIC1_ minus _NUMERIC2_. | | <figure class="highlight"> ``` NUMERIC1 * NUMERIC2 ``` </figure> | Returns _NUMERIC1_ multiplied by _NUMERIC2_. | | <figure class="highlight"> ``` NUMERIC1 / NUMERIC2 ``` </figure> | Returns _NUMERIC1_ divided by _NUMERIC2_. | | <figure class="highlight"> ``` NUMERIC1.power(NUMERIC2) ``` </figure> | Returns _NUMERIC1_ raised to the power of _NUMERIC2_. | | <figure class="highlight"> ``` NUMERIC.abs() ``` </figure> | Returns the absolute value of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC1 % NUMERIC2 ``` </figure> | Returns the remainder (modulus) of _NUMERIC1_ divided by _NUMERIC2_. The result is negative only if _numeric1_ is negative. | | <figure class="highlight"> ``` NUMERIC.sqrt() ``` </figure> | Returns the square root of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.ln() ``` </figure> | Returns the natural logarithm (base e) of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.log10() ``` </figure> | Returns the base 10 logarithm of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.log2() ``` </figure> | Returns the base 2 logarithm of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC1.log() NUMERIC1.log(NUMERIC2) ``` </figure> | 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. | | <figure class="highlight"> ``` NUMERIC.exp() ``` </figure> | Returns e raised to the power of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.ceil() ``` </figure> | Rounds _NUMERIC_ up, and returns the smallest number that is greater than or equal to _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.floor() ``` </figure> | Rounds _NUMERIC_ down, and returns the largest number that is less than or equal to _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.sin() ``` </figure> | Returns the sine of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.sinh() ``` </figure> | Returns the hyperbolic sine of _NUMERIC_.The return type is _DOUBLE_. | | <figure class="highlight"> ``` NUMERIC.cos() ``` </figure> | Returns the cosine of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.tan() ``` </figure> | Returns the tangent of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.tanh() ``` </figure> | Returns the hyperbolic tangent of _NUMERIC_.The return type is _DOUBLE_. | | <figure class="highlight"> ``` NUMERIC.cot() ``` </figure> | Returns the cotangent of a _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.asin() ``` </figure> | Returns the arc sine of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.acos() ``` </figure> | Returns the arc cosine of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.atan() ``` </figure> | Returns the arc tangent of _NUMERIC_. | | <figure class="highlight"> ``` atan2(NUMERIC1, NUMERIC2) ``` </figure> | Returns the arc tangent of a coordinate _(NUMERIC1, NUMERIC2)_. | | <figure class="highlight"> ``` NUMERIC.cosh() ``` </figure> | Returns the hyperbolic cosine of _NUMERIC_.Return value type is _DOUBLE_. | | <figure class="highlight"> ``` NUMERIC.degrees() ``` </figure> | Returns the degree representation of a radian _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.radians() ``` </figure> | Returns the radian representation of a degree _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.sign() ``` </figure> | Returns the signum of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.round(INT) ``` </figure> | Returns a number rounded to _INT_ decimal places for _NUMERIC_. | | <figure class="highlight"> ``` pi() ``` </figure> | Returns a value that is closer than any other values to pi. | | <figure class="highlight"> ``` e() ``` </figure> | Returns a value that is closer than any other values to e. | | <figure class="highlight"> ``` rand() ``` </figure> | Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive). | | <figure class="highlight"> ``` rand(INTEGER) ``` </figure> | 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. | | <figure class="highlight"> ``` randInteger(INTEGER) ``` </figure> | Returns a pseudorandom integer value between 0 (inclusive) and _INTEGER_ (exclusive). | | <figure class="highlight"> ``` randInteger(INTEGER1, INTEGER2) ``` </figure> | 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. | | <figure class="highlight"> ``` uuid() ``` </figure> | 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. | | <figure class="highlight"> ``` INTEGER.bin() ``` </figure> | 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". | | <figure class="highlight"> ``` NUMERIC.hex() STRING.hex() ``` </figure> | 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". | | Arithmetic functions | Description | | --- | --- | | <figure class="highlight"> ``` + NUMERIC ``` </figure> | Returns _NUMERIC_. | | <figure class="highlight"> ``` - NUMERIC ``` </figure> | Returns negative _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC1 + NUMERIC2 ``` </figure> | Returns _NUMERIC1_ plus _NUMERIC2_. | | <figure class="highlight"> ``` NUMERIC1 - NUMERIC2 ``` </figure> | Returns _NUMERIC1_ minus _NUMERIC2_. | | <figure class="highlight"> ``` NUMERIC1 * NUMERIC2 ``` </figure> | Returns _NUMERIC1_ multiplied by _NUMERIC2_. | | <figure class="highlight"> ``` NUMERIC1 / NUMERIC2 ``` </figure> | Returns _NUMERIC1_ divided by _NUMERIC2_. | | <figure class="highlight"> ``` NUMERIC1.power(NUMERIC2) ``` </figure> | Returns _NUMERIC1_ raised to the power of _NUMERIC2_. | | <figure class="highlight"> ``` NUMERIC.abs() ``` </figure> | Returns the absolute value of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC1 % NUMERIC2 ``` </figure> | Returns the remainder (modulus) of _NUMERIC1_ divided by _NUMERIC2_. The result is negative only if _numeric1_ is negative. | | <figure class="highlight"> ``` NUMERIC.sqrt() ``` </figure> | Returns the square root of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.ln() ``` </figure> | Returns the natural logarithm (base e) of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.log10() ``` </figure> | Returns the base 10 logarithm of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.log2() ``` </figure> | Returns the base 2 logarithm of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC1.log() NUMERIC1.log(NUMERIC2) ``` </figure> | 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. | | <figure class="highlight"> ``` NUMERIC.exp() ``` </figure> | Returns e raised to the power of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.ceil() ``` </figure> | Rounds _NUMERIC_ up, and returns the smallest number that is greater than or equal to _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.floor() ``` </figure> | Rounds _NUMERIC_ down, and returns the largest number that is less than or equal to _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.sin() ``` </figure> | Returns the sine of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.sinh() ``` </figure> | Returns the hyperbolic sine of _NUMERIC_.The return type is _DOUBLE_. | | <figure class="highlight"> ``` NUMERIC.cos() ``` </figure> | Returns the cosine of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.tan() ``` </figure> | Returns the tangent of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.tanh() ``` </figure> | Returns the hyperbolic tangent of _NUMERIC_.The return type is _DOUBLE_. | | <figure class="highlight"> ``` NUMERIC.cot() ``` </figure> | Returns the cotangent of a _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.asin() ``` </figure> | Returns the arc sine of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.acos() ``` </figure> | Returns the arc cosine of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.atan() ``` </figure> | Returns the arc tangent of _NUMERIC_. | | <figure class="highlight"> ``` atan2(NUMERIC1, NUMERIC2) ``` </figure> | Returns the arc tangent of a coordinate _(NUMERIC1, NUMERIC2)_. | | <figure class="highlight"> ``` NUMERIC.cosh() ``` </figure> | Returns the hyperbolic cosine of _NUMERIC_.Return value type is _DOUBLE_. | | <figure class="highlight"> ``` NUMERIC.degrees() ``` </figure> | Returns the degree representation of a radian _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.radians() ``` </figure> | Returns the radian representation of a degree _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.sign() ``` </figure> | Returns the signum of _NUMERIC_. | | <figure class="highlight"> ``` NUMERIC.round(INT) ``` </figure> | Returns a number rounded to _INT_ decimal places for _NUMERIC_. | | <figure class="highlight"> ``` pi() ``` </figure> | Returns a value that is closer than any other values to pi. | | <figure class="highlight"> ``` e() ``` </figure> | Returns a value that is closer than any other values to e. | | <figure class="highlight"> ``` rand() ``` </figure> | Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive). | | <figure class="highlight"> ``` rand(INTEGER) ``` </figure> | 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. | | <figure class="highlight"> ``` randInteger(INTEGER) ``` </figure> | Returns a pseudorandom integer value between 0 (inclusive) and _INTEGER_ (exclusive). | | <figure class="highlight"> ``` randInteger(INTEGER1, INTEGER2) ``` </figure> | 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. | | <figure class="highlight"> ``` uuid() ``` </figure> | 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. | | <figure class="highlight"> ``` INTEGER.bin() ``` </figure> | 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". | | <figure class="highlight"> ``` NUMERIC.hex() STRING.hex() ``` </figure> | 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 | | --- | --- | | <figure class="highlight"> ``` string1 || string2 ``` </figure> | Returns the concatenation of _string1_ and _string2_. | | <figure class="highlight"> ``` CHAR_LENGTH(string) CHARACTER_LENGTH(string) ``` </figure> | Returns the number of characters in _string_. | | <figure class="highlight"> ``` UPPER(string) ``` </figure> | Returns _string_ in uppercase. | | <figure class="highlight"> ``` LOWER(string) ``` </figure> | Returns _string_ in lowercase. | | <figure class="highlight"> ``` POSITION(string1 IN string2) ``` </figure> | Returns the position (start from 1) of the first occurrence of _string1_ in _string2_; returns 0 if _string1_ cannot be found in _string2_. | | <figure class="highlight"> ``` TRIM([ BOTH | LEADING | TRAILING ] string1 FROM string2) ``` </figure> | Returns a string that removes leading and/or trailing characters _string1_ from _string2_. By default, whitespaces at both sides are removed. | | <figure class="highlight"> ``` LTRIM(string) ``` </figure> | Returns a string that removes the left whitespaces from _string_.E.g., `LTRIM(' This is a test String.')` returns "This is a test String.". | | <figure class="highlight"> ``` RTRIM(string) ``` </figure> | Returns a string that removes the right whitespaces from _string_.E.g., `RTRIM('This is a test String. ')` returns "This is a test String.". | | <figure class="highlight"> ``` REPEAT(string, integer) ``` </figure> | 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.". | | <figure class="highlight"> ``` REGEXP_REPLACE(string1, string2, string3) ``` </figure> | 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". | | <figure class="highlight"> ``` OVERLAY(string1 PLACING string2 FROM integer1 [ FOR integer2 ]) ``` </figure> | 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" | | <figure class="highlight"> ``` SUBSTRING(string FROM integer1 [ FOR integer2 ]) ``` </figure> | Returns a substring of _string_ starting from position _integer1_ with length _integer2_ (to the end by default). | | <figure class="highlight"> ``` REPLACE(string1, string2, string3) ``` </figure> | 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". | | <figure class="highlight"> ``` REGEXP_EXTRACT(string1, string2[, integer]) ``` </figure> | 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". | | <figure class="highlight"> ``` INITCAP(string) ``` </figure> | 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. | | <figure class="highlight"> ``` CONCAT(string1, string2,...) ``` </figure> | Returns a string that concatenates _string1, string2, ..._. Returns NULL if any argument is NULL.E.g., `CONCAT('AA', 'BB', 'CC')` returns "AABBCC". | | <figure class="highlight"> ``` CONCAT_WS(string1, string2, string3,...) ``` </figure> | 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". | | <figure class="highlight"> ``` LPAD(string1, integer, string2) ``` </figure> | 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". | | <figure class="highlight"> ``` RPAD(string1, integer, string2) ``` </figure> | 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". | | <figure class="highlight"> ``` FROM_BASE64(string) ``` </figure> | Returns the base64-decoded result from _string_; returns NULL if _string_ is NULL.E.g., `FROM_BASE64('aGVsbG8gd29ybGQ=')` returns "hello world". | | <figure class="highlight"> ``` TO_BASE64(string) ``` </figure> | Returns the base64-encoded result from _string_; returns NULL if _string_ is NULL.E.g., `TO_BASE64('hello world')` returns "aGVsbG8gd29ybGQ=". | | String functions | Description | | --- | --- | | <figure class="highlight"> ``` STRING1 + STRING2 ``` </figure> | Returns the concatenation of _STRING1_ and _STRING2_. | | <figure class="highlight"> ``` STRING.charLength() ``` </figure> | Returns the number of characters in _STRING_. | | <figure class="highlight"> ``` STRING.upperCase() ``` </figure> | Returns _STRING_ in uppercase. | | <figure class="highlight"> ``` STRING.lowerCase() ``` </figure> | Returns _STRING_ in lowercase. | | <figure class="highlight"> ``` STRING1.position(STRING2) ``` </figure> | Returns the position (start from 1) of the first occurrence of _STRING1_ in _STRING2_; returns 0 if _STRING1_ cannot be found in _STRING2_. | | <figure class="highlight"> ``` STRING1.trim(LEADING, STRING2) STRING1.trim(TRAILING, STRING2) STRING1.trim(BOTH, STRING2) STRING1.trim(BOTH) STRING1.trim() ``` </figure> | Returns a string that removes leading and/or trailing characters _STRING2_ from _STRING1_. By default, whitespaces at both sides are removed. | | <figure class="highlight"> ``` STRING.ltrim() ``` </figure> | Returns a string that removes the left whitespaces from _STRING_.E.g., `' This is a test String.'.ltrim()` returns "This is a test String.". | | <figure class="highlight"> ``` STRING.rtrim() ``` </figure> | Returns a string that removes the right whitespaces from _STRING_.E.g., `'This is a test String. '.rtrim()` returns "This is a test String.". | | <figure class="highlight"> ``` STRING.repeat(INT) ``` </figure> | 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.". | | <figure class="highlight"> ``` STRING1.regexpReplace(STRING2, STRING3) ``` </figure> | 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". | | <figure class="highlight"> ``` STRING1.overlay(STRING2, INT1) STRING1.overlay(STRING2, INT1, INT2) ``` </figure> | 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". | | <figure class="highlight"> ``` STRING.substring(INT1) STRING.substring(INT1, INT2) ``` </figure> | Returns a substring of _STRING_ starting from position _INT1_ with length _INT2_ (to the end by default). | | <figure class="highlight"> ``` STRING1.replace(STRING2, STRING3) ``` </figure> | 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'. | | <figure class="highlight"> ``` STRING1.regexpExtract(STRING2[, INTEGER1]) ``` </figure> | 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". | | <figure class="highlight"> ``` STRING.initCap() ``` </figure> | 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. | | <figure class="highlight"> ``` concat(STRING1, STRING2, ...) ``` </figure> | Returns a string that concatenates _STRING1, STRING2, ..._. Returns NULL if any argument is NULL.E.g., `concat('AA', 'BB', 'CC')` returns "AABBCC". | | <figure class="highlight"> ``` concat_ws(STRING1, STRING2, STRING3, ...) ``` </figure> | 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". | | <figure class="highlight"> ``` STRING1.lpad(INT, STRING2) ``` </figure> | 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". | | <figure class="highlight"> ``` STRING1.rpad(INT, STRING2) ``` </figure> | 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". | | <figure class="highlight"> ``` STRING.fromBase64() ``` </figure> | Returns the base64-decoded result from _STRING_; returns NULL if _STRING_ is NULL.E.g., `'aGVsbG8gd29ybGQ='.fromBase64()` returns "hello world". | | <figure class="highlight"> ``` STRING.toBase64() ``` </figure> | Returns the base64-encoded result from _STRING_; returns NULL if _STRING_ is NULL.E.g., `'hello world'.toBase64()` returns "aGVsbG8gd29ybGQ=". | | String functions | Description | | --- | --- | | <figure class="highlight"> ``` STRING1 + STRING2 ``` </figure> | Returns the concatenation of _STRING1_ and _STRING2_. | | <figure class="highlight"> ``` STRING.charLength() ``` </figure> | Returns the number of characters in _STRING_. | | <figure class="highlight"> ``` STRING.upperCase() ``` </figure> | Returns _STRING_ in uppercase. | | <figure class="highlight"> ``` STRING.lowerCase() ``` </figure> | Returns _STRING_ in lowercase. | | <figure class="highlight"> ``` STRING1.position(STRING2) ``` </figure> | Returns the position (start from 1) of the first occurrence of _STRING1_ in _STRING2_; returns 0 if _STRING1_ cannot be found in _STRING2_. | | <figure class="highlight"> ``` STRING.trim( leading = true, trailing = true, character = " ") ``` </figure> | Returns a string that removes leading and/or trailing characters from _STRING_. | | <figure class="highlight"> ``` STRING.ltrim() ``` </figure> | Returns a string that removes the left whitespaces from _STRING_.E.g., `" This is a test String.".ltrim()` returns "This is a test String.". | | <figure class="highlight"> ``` STRING.rtrim() ``` </figure> | Returns a string that removes the right whitespaces from _STRING_.E.g., `"This is a test String. ".rtrim()` returns "This is a test String.". | | <figure class="highlight"> ``` STRING.repeat(INT) ``` </figure> | 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.". | | <figure class="highlight"> ``` STRING1.regexpReplace(STRING2, STRING3) ``` </figure> | 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". | | <figure class="highlight"> ``` STRING1.overlay(STRING2, INT1) STRING1.overlay(STRING2, INT1, INT2) ``` </figure> | 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". | | <figure class="highlight"> ``` STRING.substring(INT1) STRING.substring(INT1, INT2) ``` </figure> | Returns a substring of _STRING_ starting from position _INT1_ with length _INT2_ (to the end by default). | | <figure class="highlight"> ``` STRING1.replace(STRING2, STRING3) ``` </figure> | 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". | | <figure class="highlight"> ``` STRING1.regexpExtract(STRING2[, INTEGER1]) ``` </figure> | 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". | | <figure class="highlight"> ``` STRING.initCap() ``` </figure> | 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. | | <figure class="highlight"> ``` concat(STRING1, STRING2, ...) ``` </figure> | Returns a string that concatenates _STRING1, STRING2, ..._. Returns NULL if any argument is NULL.E.g., `concat("AA", "BB", "CC")` returns "AABBCC". | | <figure class="highlight"> ``` concat_ws(STRING1, STRING2, STRING3, ...) ``` </figure> | 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". | | <figure class="highlight"> ``` STRING1.lpad(INT, STRING2) ``` </figure> | 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". | | <figure class="highlight"> ``` STRING1.rpad(INT, STRING2) ``` </figure> | 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". | | <figure class="highlight"> ``` STRING.fromBase64() ``` </figure> | Returns the base64-decoded result from _STRING_; returns null If _STRING_ is NULL.E.g., `"aGVsbG8gd29ybGQ=".fromBase64()` returns "hello world". | | <figure class="highlight"> ``` STRING.toBase64() ``` </figure> | 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 | | --- | --- | | <figure class="highlight"> ``` DATE string ``` </figure> | Returns a SQL date parsed from _string_ in form of "yyyy-MM-dd". | | <figure class="highlight"> ``` TIME string ``` </figure> | Returns a SQL time parsed from _string_ in form of "HH:mm:ss". | | <figure class="highlight"> ``` TIMESTAMP string ``` </figure> | Returns a SQL timestamp parsed from _string_ in form of "yyyy-MM-dd HH:mm:ss[.SSS]". | | <figure class="highlight"> ``` INTERVAL string range ``` </figure> | 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. | | <figure class="highlight"> ``` CURRENT_DATE ``` </figure> | Returns the current SQL date in the UTC time zone. | | <figure class="highlight"> ``` CURRENT_TIME ``` </figure> | Returns the current SQL time in the UTC time zone. | | <figure class="highlight"> ``` CURRENT_TIMESTAMP ``` </figure> | Returns the current SQL timestamp in the UTC time zone. | | <figure class="highlight"> ``` LOCALTIME ``` </figure> | Returns the current SQL time in local time zone. | | <figure class="highlight"> ``` LOCALTIMESTAMP ``` </figure> | Returns the current SQL timestamp in local time zone. | | <figure class="highlight"> ``` EXTRACT(timeintervalunit FROM temporal) ``` </figure> | Returns a long value extracted from the _timeintervalunit_ part of _temporal_.E.g., `EXTRACT(DAY FROM DATE '2006-06-05')` returns 5. | | <figure class="highlight"> ``` YEAR(date) ``` </figure> | Returns the year from SQL date _date_. Equivalent to EXTRACT(YEAR FROM date).E.g., `YEAR(DATE '1994-09-27')` returns 1994. | | <figure class="highlight"> ``` QUARTER(date) ``` </figure> | 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. | | <figure class="highlight"> ``` MONTH(date) ``` </figure> | 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. | | <figure class="highlight"> ``` WEEK(date) ``` </figure> | 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. | | <figure class="highlight"> ``` DAYOFYEAR(date) ``` </figure> | 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. | | <figure class="highlight"> ``` DAYOFMONTH(date) ``` </figure> | 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. | | <figure class="highlight"> ``` DAYOFWEEK(date) ``` </figure> | 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. | | <figure class="highlight"> ``` HOUR(timestamp) ``` </figure> | 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. | | <figure class="highlight"> ``` MINUTE(timestamp) ``` </figure> | 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. | | <figure class="highlight"> ``` SECOND(timestamp) ``` </figure> | 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. | | <figure class="highlight"> ``` FLOOR(timepoint TO timeintervalunit) ``` </figure> | 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. | | <figure class="highlight"> ``` CEIL(timepoint TO timeintervalunit) ``` </figure> | 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. | | <figure class="highlight"> ``` (timepoint1, temporal1) OVERLAPS (timepoint2, temporal2) ``` </figure> | 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. | | <figure class="highlight"> ``` DATE_FORMAT(timestamp, string) ``` </figure> | 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. | | <figure class="highlight"> ``` TIMESTAMPADD(timeintervalunit, interval, timepoint) ``` </figure> | 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`. | | <figure class="highlight"> ``` TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2) ``` </figure> | 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](#time-interval-and-point-unit-specifiers).E.g., `TIMESTAMPDIFF(DAY, TIMESTAMP '2003-01-02 10:00:00', TIMESTAMP '2003-01-03 10:00:00')` leads to `1`. | | Temporal functions | Description | | --- | --- | | <figure class="highlight"> ``` STRING.toDate() ``` </figure> | Returns a SQL date parsed from _STRING_ in form of "yyyy-MM-dd". | | <figure class="highlight"> ``` STRING.toTime() ``` </figure> | Returns a SQL time parsed from _STRING_ in form of "HH:mm:ss". | | <figure class="highlight"> ``` STRING.toTimestamp() ``` </figure> | Returns a SQL timestamp parsed from _STRING_ in form of "yyyy-MM-dd HH:mm:ss[.SSS]". | | <figure class="highlight"> ``` NUMERIC.year NUMERIC.years ``` </figure> | Creates an interval of months for _NUMERIC_ years. | | <figure class="highlight"> ``` NUMERIC.quarter NUMERIC.quarters ``` </figure> | Creates an interval of months for _NUMERIC_ quarters.E.g., `2.quarters` returns 6. | | <figure class="highlight"> ``` NUMERIC.month NUMERIC.months ``` </figure> | Creates an interval of _NUMERIC_ months. | | <figure class="highlight"> ``` NUMERIC.week NUMERIC.weeks ``` </figure> | Creates an interval of milliseconds for _NUMERIC_ weeks.E.g., `2.weeks` returns 1209600000. | | <figure class="highlight"> ``` NUMERIC.day NUMERIC.days ``` </figure> | Creates an interval of milliseconds for _NUMERIC_ days. | | <figure class="highlight"> ``` NUMERIC.hour NUMERIC.hours ``` </figure> | Creates an interval of milliseconds for _NUMERIC_ hours. | | <figure class="highlight"> ``` NUMERIC.minute NUMERIC.minutes ``` </figure> | Creates an interval of milliseconds for _NUMERIC_ minutes. | | <figure class="highlight"> ``` NUMERIC.second NUMERIC.seconds ``` </figure> | Creates an interval of milliseconds for _NUMERIC_ seconds. | | <figure class="highlight"> ``` NUMERIC.milli NUMERIC.millis ``` </figure> | Creates an interval of _NUMERIC_ milliseconds. | | <figure class="highlight"> ``` currentDate() ``` </figure> | Returns the current SQL date in the UTC time zone. | | <figure class="highlight"> ``` currentTime() ``` </figure> | Returns the current SQL time in the UTC time zone. | | <figure class="highlight"> ``` currentTimestamp() ``` </figure> | Returns the current SQL timestamp in the UTC time zone. | | <figure class="highlight"> ``` localTime() ``` </figure> | Returns the current SQL time in local time zone. | | <figure class="highlight"> ``` localTimestamp() ``` </figure> | Returns the current SQL timestamp in local time zone. | | <figure class="highlight"> ``` TEMPORAL.extract(TIMEINTERVALUNIT) ``` </figure> | 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. | | <figure class="highlight"> ``` TIMEPOINT.floor(TIMEINTERVALUNIT) ``` </figure> | Returns a value that rounds _TIMEPOINT_ down to the time unit _TIMEINTERVALUNIT_.E.g., `'12:44:31'.toDate.floor(MINUTE)` returns 12:44:00. | | <figure class="highlight"> ``` TIMEPOINT.ceil(TIMEINTERVALUNIT) ``` </figure> | Returns a value that rounds _TIMEPOINT_ up to the time unit _TIMEINTERVALUNIT_.E.g., `'12:44:31'.toTime.floor(MINUTE)` returns 12:45:00. | | <figure class="highlight"> ``` temporalOverlaps(TIMEPOINT1, TEMPORAL1, TIMEPOINT2, TEMPORAL2) ``` </figure> | 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. | | <figure class="highlight"> ``` dateFormat(TIMESTAMP, STRING) ``` </figure> | 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. | | <figure class="highlight"> ``` timestampDiff(TIMEPOINTUNIT, TIMEPOINT1, TIMEPOINT2) ``` </figure> | 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](#time-interval-and-point-unit-specifiers).E.g., `timestampDiff(DAY, '2003-01-02 10:00:00'.toTimestamp, '2003-01-03 10:00:00'.toTimestamp)` leads to `1`. | | Temporal functions | Description | | --- | --- | | <figure class="highlight"> ``` STRING.toDate ``` </figure> | Returns a SQL date parsed from _STRING_ in form of "yyyy-MM-dd". | | <figure class="highlight"> ``` STRING.toTime ``` </figure> | Returns a SQL time parsed from _STRING_ in form of "HH:mm:ss". | | <figure class="highlight"> ``` STRING.toTimestamp ``` </figure> | Returns a SQL timestamp parsed from _STRING_ in form of "yyyy-MM-dd HH:mm:ss[.SSS]". | | <figure class="highlight"> ``` NUMERIC.year NUMERIC.years ``` </figure> | Creates an interval of months for _NUMERIC_ years. | | <figure class="highlight"> ``` NUMERIC.quarter NUMERIC.quarters ``` </figure> | Creates an interval of months for _NUMERIC_ quarters.E.g., `2.quarters` returns 6. | | <figure class="highlight"> ``` NUMERIC.month NUMERIC.months ``` </figure> | Creates an interval of _NUMERIC_ months. | | <figure class="highlight"> ``` NUMERIC.week NUMERIC.weeks ``` </figure> | Creates an interval of milliseconds for _NUMERIC_ weeks.E.g., `2.weeks` returns 1209600000. | | <figure class="highlight"> ``` NUMERIC.day NUMERIC.days ``` </figure> | Creates an interval of milliseconds for _NUMERIC_ days. | | <figure class="highlight"> ``` NUMERIC.hour NUMERIC.hours ``` </figure> | Creates an interval of milliseconds for _NUMERIC_ hours. | | <figure class="highlight"> ``` NUMERIC.minute NUMERIC.minutes ``` </figure> | Creates an interval of milliseconds for _NUMERIC_ minutes. | | <figure class="highlight"> ``` NUMERIC.second NUMERIC.seconds ``` </figure> | Creates an interval of milliseconds for _NUMERIC_ seconds. | | <figure class="highlight"> ``` NUMERIC.milli NUMERIC.millis ``` </figure> | Creates an interval of _NUMERIC_ milliseconds. | | <figure class="highlight"> ``` currentDate() ``` </figure> | Returns the current SQL date in the UTC time zone. | | <figure class="highlight"> ``` currentTime() ``` </figure> | Returns the current SQL time in the UTC time zone. | | <figure class="highlight"> ``` currentTimestamp() ``` </figure> | Returns the current SQL timestamp in the UTC time zone. | | <figure class="highlight"> ``` localTime() ``` </figure> | Returns the current SQL time in local time zone. | | <figure class="highlight"> ``` localTimestamp() ``` </figure> | Returns the current SQL timestamp in local time zone. | | <figure class="highlight"> ``` TEMPORAL.extract(TIMEINTERVALUNIT) ``` </figure> | 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. | | <figure class="highlight"> ``` TIMEPOINT.floor(TIMEINTERVALUNIT) ``` </figure> | 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. | | <figure class="highlight"> ``` TIMEPOINT.ceil(TIMEINTERVALUNIT) ``` </figure> | 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. | | <figure class="highlight"> ``` temporalOverlaps(TIMEPOINT1, TEMPORAL1, TIMEPOINT2, TEMPORAL2) ``` </figure> | 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. | | <figure class="highlight"> ``` dateFormat(TIMESTAMP, STRING) ``` </figure> | 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. | | <figure class="highlight"> ``` timestampDiff(TIMEPOINTUNIT, TIMEPOINT1, TIMEPOINT2) ``` </figure> | 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](#time-interval-and-point-unit-specifiers).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 | | --- | --- | | <figure class="highlight"> ``` CASE value WHEN value1_1 [, value1_2 ]* THEN result1 [ WHEN value2_1 [, value2_2 ]* THEN result2 ]* [ ELSE resultZ ] END ``` </figure> | 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. | | <figure class="highlight"> ``` CASE WHEN condition1 THEN result1 [ WHEN condition2 THEN result2 ]* [ ELSE resultZ ] END ``` </figure> | Returns _resultX_ when the first _conditionX_ is met. When no condition is met, returns _resultZ_ if it is provided and returns NULL otherwise. | | <figure class="highlight"> ``` NULLIF(value1, value2) ``` </figure> | Returns NULL if _value1_ is equal to _value2_; returns _value1_ otherwise.E.g., `NULLIF(5, 5)` returns NULL; `NULLIF(5, 0)` returns 5. | | <figure class="highlight"> ``` COALESCE(value1, value2 [, value3 ]* ) ``` </figure> | Returns the first value that is not NULL from _value1, value2, ..._.E.g., `COALESCE(NULL, 5)` returns 5. | | Conditional functions | Description | | --- | --- | | <figure class="highlight"> ``` BOOLEAN.?(VALUE1, VALUE2) ``` </figure> | Returns _VALUE1_ if _BOOLEAN_ evaluates to TRUE; returns _VALUE2_ otherwise.E.g., `(42 > 5).?('A', 'B')` returns "A". | | Conditional functions | Description | | --- | --- | | <figure class="highlight"> ``` BOOLEAN.?(VALUE1, VALUE2) ``` </figure> | 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 | | --- | --- | | <figure class="highlight"> ``` CAST(value AS type) ``` </figure> | Returns a new _value_ being cast to type _type_. See the supported types [here](sql.html#data-types).E.g., `CAST('42' AS INT)` returns 42; `CAST(NULL AS VARCHAR)` returns NULL of type VARCHAR. | | Type conversion functions | Description | | --- | --- | | <figure class="highlight"> ``` ANY.cast(TYPE) ``` </figure> | Returns a new _ANY_ being cast to type _TYPE_. See the supported types [here](tableApi.html#data-types).E.g., `'42'.cast(INT)` returns 42; `Null(STRING)` returns NULL of type STRING. | | Type conversion functions | Description | | --- | --- | | <figure class="highlight"> ``` ANY.cast(TYPE) ``` </figure> | Returns a new _ANY_ being cast to type _TYPE_. See the supported types [here](tableApi.html#data-types).E.g., `"42".cast(Types.INT)` returns 42; `Null(Types.STRING)` returns NULL of type STRING. | ### Collection Functions | Collection functions | Description | | --- | --- | | <figure class="highlight"> ``` CARDINALITY(array) ``` </figure> | Returns the number of elements in _array_. | | <figure class="highlight"> ``` array ‘[’ integer ‘]’ ``` </figure> | Returns the element at position _integer_ in _array_. The index starts from 1. | | <figure class="highlight"> ``` ELEMENT(array) ``` </figure> | 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. | | <figure class="highlight"> ``` CARDINALITY(map) ``` </figure> | Returns the number of entries in _map_. | | <figure class="highlight"> ``` map ‘[’ value ‘]’ ``` </figure> | Returns the value specified by key _value_ in _map_. | | Collection functions | Description | | --- | --- | | <figure class="highlight"> ``` ARRAY.cardinality() ``` </figure> | Returns the number of elements in _ARRAY_. | | <figure class="highlight"> ``` ARRAY.at(INT) ``` </figure> | Returns the element at position _INT_ in _ARRAY_. The index starts from 1. | | <figure class="highlight"> ``` ARRAY.element() ``` </figure> | 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. | | <figure class="highlight"> ``` MAP.cardinality() ``` </figure> | Returns the number of entries in _MAP_. | | <figure class="highlight"> ``` MAP.at(ANY) ``` </figure> | Returns the value specified by key _ANY_ in _MAP_. | | Collection functions | Description | | --- | --- | | <figure class="highlight"> ``` ARRAY.cardinality() ``` </figure> | Returns the number of elements in _ARRAY_. | | <figure class="highlight"> ``` ARRAY.at(INT) ``` </figure> | Returns the element at position _INT_ in _ARRAY_. The index starts from 1. | | <figure class="highlight"> ``` ARRAY.element() ``` </figure> | 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. | | <figure class="highlight"> ``` MAP.cardinality() ``` </figure> | Returns the number of entries in _MAP_. | | <figure class="highlight"> ``` MAP.at(ANY) ``` </figure> | Returns the value specified by key _ANY_ in _MAP_. | ### Value Construction Functions | Value construction functions | Description | | --- | --- | | <figure class="highlight"> ``` ROW(value1, [, value2]*) (value1, [, value2]*) ``` </figure> | Returns a row created from a list of values (_value1, value2,_...). | | <figure class="highlight"> ``` ARRAY ‘[’ value1 [, value2 ]* ‘]’ ``` </figure> | Returns an array created from a list of values (_value1, value2_, ...). | | <figure class="highlight"> ``` MAP ‘[’ value1, value2 [, value3, value4 ]* ‘]’ ``` </figure> | Returns a map created from a list of key-value pairs ((_value1, value2_), _(value3, value4)_, ...). | | Value constructor functions | Description | | --- | --- | | <figure class="highlight"> ``` row(ANY1, ANY2, ...) ``` </figure> | Returns a row created from a list of object values (_ANY1, ANY2_, ...). Row is composite type that can be access via [value access functions](#value-access-functions). | | <figure class="highlight"> ``` array(ANY1, ANY2, ...) ``` </figure> | Returns an array created from a list of object values (_ANY1, ANY2_, ...). | | <figure class="highlight"> ``` map(ANY1, ANY2, ANY3, ANY4, ...) ``` </figure> | Returns a map created from a list of key-value pairs ((_ANY1, ANY2_), _(ANY3, ANY4)_, ...). | | <figure class="highlight"> ``` NUMERIC.rows ``` </figure> | Creates a _NUMERIC_ interval of rows (commonly used in window creation). | | Value constructor functions | Description | | --- | --- | | <figure class="highlight"> ``` row(ANY1, ANY2, ...) ``` </figure> | Returns a row created from a list of object values (_ANY1, ANY2_, ...). Row is composite type that can be access via [value access functions](#value-access-functions). | | <figure class="highlight"> ``` array(ANY1, ANY2, ...) ``` </figure> | Returns an array created from a list of object values (_ANY1, ANY2_, ...). | | <figure class="highlight"> ``` map(ANY1, ANY2, ANY3, ANY4, ...) ``` </figure> | Returns a map created from a list of key-value pairs ((_ANY1, ANY2_), _(ANY3, ANY4)_, ...). | | <figure class="highlight"> ``` NUMERIC.rows ``` </figure> | Creates a _NUMERIC_ interval of rows (commonly used in window creation). | ### Value Access Functions | Value access functions | Description | | --- | --- | | <figure class="highlight"> ``` tableName.compositeType.field ``` </figure> | Returns the value of a field from a Flink composite type (e.g., Tuple, POJO) by name. | | <figure class="highlight"> ``` tableName.compositeType.* ``` </figure> | 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 | | --- | --- | | <figure class="highlight"> ``` COMPOSITE.get(STRING) COMPOSITE.get(INT) ``` </figure> | 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)`. | | <figure class="highlight"> ``` ANY.flatten() ``` </figure> | 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 | | --- | --- | | <figure class="highlight"> ``` COMPOSITE.get(STRING) COMPOSITE.get(INT) ``` </figure> | 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)`. | | <figure class="highlight"> ``` ANY.flatten() ``` </figure> | 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`). | ### Grouping Functions | Grouping functions | Description | | --- | --- | | <figure class="highlight"> ``` GROUP_ID() ``` </figure> | Returns an integer that uniquely identifies the combination of grouping keys. | | <figure class="highlight"> ``` GROUPING(expression1 [, expression2]* ) GROUPING_ID(expression1 [, expression2]* ) ``` </figure> | Returns a bit vector of the given grouping expressions. | | Grouping functions | Description | | --- | --- | | Grouping functions | Description | | --- | --- | ### Hash Functions | Hash functions | Description | | --- | --- | | <figure class="highlight"> ``` MD5(string) ``` </figure> | Returns the MD5 hash of _string_ as a string of 32 hexadecimal digits; returns NULL if _string_ is NULL. | | <figure class="highlight"> ``` SHA1(string) ``` </figure> | Returns the SHA-1 hash of _string_ as a string of 40 hexadecimal digits; returns NULL if _string_ is NULL. | | <figure class="highlight"> ``` SHA224(string) ``` </figure> | Returns the SHA-224 hash of _string_ as a string of 56 hexadecimal digits; returns NULL if _string_ is NULL. | | <figure class="highlight"> ``` SHA256(string) ``` </figure> | Returns the SHA-256 hash of _string_ as a string of 64 hexadecimal digits; returns NULL if _string_ is NULL. | | <figure class="highlight"> ``` SHA384(string) ``` </figure> | Returns the SHA-384 hash of _string_ as a string of 96 hexadecimal digits; returns NULL if _string_ is NULL. | | <figure class="highlight"> ``` SHA512(string) ``` </figure> | Returns the SHA-512 hash of _string_ as a string of 128 hexadecimal digits; returns NULL if _string_ is NULL. | | <figure class="highlight"> ``` SHA2(string, hashLength) ``` </figure> | 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 | | --- | --- | | <figure class="highlight"> ``` STRING.md5() ``` </figure> | Returns the MD5 hash of _STRING_ as a string of 32 hexadecimal digits; returns NULL if _STRING_ is NULL. | | <figure class="highlight"> ``` STRING.sha1() ``` </figure> | Returns the SHA-1 hash of _STRING_ as a string of 40 hexadecimal digits; returns NULL if _STRING_ is NULL. | | <figure class="highlight"> ``` STRING.sha224() ``` </figure> | Returns the SHA-224 hash of _STRING_ as a string of 56 hexadecimal digits; returns NULL if _STRING_ is NULL. | | <figure class="highlight"> ``` STRING.sha256() ``` </figure> | Returns the SHA-256 hash of _STRING_ as a string of 64 hexadecimal digits; returns NULL if _STRING_ is NULL. | | <figure class="highlight"> ``` STRING.sha384() ``` </figure> | Returns the SHA-384 hash of _STRING_ as a string of 96 hexadecimal digits; returns NULL if _STRING_ is NULL. | | <figure class="highlight"> ``` STRING.sha512() ``` </figure> | Returns the SHA-512 hash of _STRING_ as a string of 128 hexadecimal digits; returns NULL if _STRING_ is NULL. | | <figure class="highlight"> ``` STRING.sha2(INT) ``` </figure> | 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 | | --- | --- | | <figure class="highlight"> ``` STRING.md5() ``` </figure> | Returns the MD5 hash of _STRING_ as a string of 32 hexadecimal digits; returns NULL if _STRING_ is NULL. | | <figure class="highlight"> ``` STRING.sha1() ``` </figure> | Returns the SHA-1 hash of _STRING_ as a string of 40 hexadecimal digits; returns NULL if _STRING_ is NULL. | | <figure class="highlight"> ``` STRING.sha224() ``` </figure> | Returns the SHA-224 hash of _STRING_ as a string of 56 hexadecimal digits; returns NULL if _STRING_ is NULL. | | <figure class="highlight"> ``` STRING.sha256() ``` </figure> | Returns the SHA-256 hash of _STRING_ as a string of 64 hexadecimal digits; returns NULL if _STRING_ is NULL. | | <figure class="highlight"> ``` STRING.sha384() ``` </figure> | Returns the SHA-384 hash of _STRING_ as a string of 96 hexadecimal digits; returns NULL if _STRING_ is NULL. | | <figure class="highlight"> ``` STRING.sha512() ``` </figure> | Returns the SHA-512 hash of _STRING_ as a string of 128 hexadecimal digits; returns NULL if _STRING_ is NULL. | | <figure class="highlight"> ``` STRING.sha2(INT) ``` </figure> | 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 | | --- | --- | | <figure class="highlight"> ``` ANY.as(NAME1, NAME2, ...) ``` </figure> | Specifies a name for _ANY_ (a field). Additional names can be specified if the expression expands to multiple fields. | | Auxiliary functions | Description | | --- | --- | | <figure class="highlight"> ``` ANY.as(NAME1, NAME2, ...) ``` </figure> | 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 | | --- | --- | | <figure class="highlight"> ``` COUNT([ ALL ] expression | DISTINCT expression1 [, expression2]*) ``` </figure> | 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. | | <figure class="highlight"> ``` COUNT(*) COUNT(1) ``` </figure> | Returns the number of input rows. | | <figure class="highlight"> ``` AVG([ ALL | DISTINCT ] expression) ``` </figure> | 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. | | <figure class="highlight"> ``` SUM([ ALL | DISTINCT ] expression) ``` </figure> | By default or with keyword ALL, returns the sum of _expression_ across all input rows. Use DISTINCT for one unique instance of each value. | | <figure class="highlight"> ``` MAX([ ALL | DISTINCT ] expression) ``` </figure> | 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. | | <figure class="highlight"> ``` MIN([ ALL | DISTINCT ] expression) ``` </figure> | 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. | | <figure class="highlight"> ``` STDDEV_POP([ ALL | DISTINCT ] expression) ``` </figure> | 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. | | <figure class="highlight"> ``` STDDEV_SAMP([ ALL | DISTINCT ] expression) ``` </figure> | 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. | | <figure class="highlight"> ``` VAR_POP([ ALL | DISTINCT ] expression) ``` </figure> | 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. | | <figure class="highlight"> ``` VAR_SAMP([ ALL | DISTINCT ] expression) ``` </figure> | 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. | | <figure class="highlight"> ``` COLLECT([ ALL | DISTINCT ] expression) ``` </figure> | 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. | | Aggregate functions | Description | | --- | --- | | <figure class="highlight"> ``` FIELD.count ``` </figure> | Returns the number of input rows for which _FIELD_ is not NULL. | | <figure class="highlight"> ``` FIELD.avg ``` </figure> | Returns the average (arithmetic mean) of _FIELD_ across all input rows. | | <figure class="highlight"> ``` FIELD.sum ``` </figure> | Returns the sum of numeric field _FIELD_ across all input rows. If all values are NULL, returns NULL. | | <figure class="highlight"> ``` FIELD.sum0 ``` </figure> | Returns the sum of numeric field _FIELD_ across all input rows. If all values are NULL, returns 0. | | <figure class="highlight"> ``` FIELD.max ``` </figure> | Returns the maximum value of numeric field _FIELD_ across all input rows. | | <figure class="highlight"> ``` FIELD.min ``` </figure> | Returns the minimum value of numeric field _FIELD_ across all input rows. | | <figure class="highlight"> ``` FIELD.stddevPop ``` </figure> | Returns the population standard deviation of numeric field _FIELD_ across all input rows. | | <figure class="highlight"> ``` FIELD.stddevSamp ``` </figure> | Returns the sample standard deviation of numeric field _FIELD_ across all input rows. | | <figure class="highlight"> ``` FIELD.varPop ``` </figure> | Returns the population variance (square of the population standard deviation) of numeric field _FIELD_ across all input rows. | | <figure class="highlight"> ``` FIELD.varSamp ``` </figure> | Returns the sample variance (square of the sample standard deviation) of numeric field _FIELD_ across all input rows. | | <figure class="highlight"> ``` FIELD.collect ``` </figure> | Returns a multiset of _FIELD_ across all input rows. | | Aggregate functions | Description | | --- | --- | | <figure class="highlight"> ``` FIELD.count ``` </figure> | Returns the number of input rows for which _FIELD_ is not NULL. | | <figure class="highlight"> ``` FIELD.avg ``` </figure> | Returns the average (arithmetic mean) of _FIELD_ across all input rows. | | <figure class="highlight"> ``` FIELD.sum ``` </figure> | Returns the sum of numeric field _FIELD_ across all input rows. If all values are NULL, returns NULL. | | <figure class="highlight"> ``` FIELD.sum0 ``` </figure> | Returns the sum of numeric field _FIELD_ across all input rows. If all values are NULL, returns 0. | | <figure class="highlight"> ``` FIELD.max ``` </figure> | Returns the maximum value of numeric field _FIELD_ across all input rows. | | <figure class="highlight"> ``` FIELD.min ``` </figure> | Returns the minimum value of numeric field _FIELD_ across all input rows. | | <figure class="highlight"> ``` FIELD.stddevPop ``` </figure> | Returns the population standard deviation of numeric field _FIELD_ across all input rows. | | <figure class="highlight"> ``` FIELD.stddevSamp ``` </figure> | Returns the sample standard deviation of numeric field _FIELD_ across all input rows. | | <figure class="highlight"> ``` FIELD.varPop ``` </figure> | Returns the population variance (square of the population standard deviation) of numeric field _FIELD_ across all input rows. | | <figure class="highlight"> ``` FIELD.varSamp ``` </figure> | Returns the sample variance (square of the sample standard deviation) of numeric field _FIELD_ across all input rows. | | <figure class="highlight"> ``` FIELD.collect ``` </figure> | Returns a multiset of _FIELD_ across all input rows. | ## Date Format Specifiers The following table lists specifiers for date format functions. | Specifier | Description | | --- | --- | | <figure class="highlight"> ``` %a ``` </figure> | Abbreviated weekday name (`Sun` .. `Sat`) | | <figure class="highlight"> ``` %b ``` </figure> | Abbreviated month name (`Jan` .. `Dec`) | | <figure class="highlight"> ``` %c ``` </figure> | Month, numeric (`1` .. `12`) | | <figure class="highlight"> ``` %D ``` </figure> | Day of the month with English suffix (`0th`, `1st`, `2nd`, `3rd`, ...) | | <figure class="highlight"> ``` %d ``` </figure> | Day of the month, numeric (`01` .. `31`) | | <figure class="highlight"> ``` %e ``` </figure> | Day of the month, numeric (`1` .. `31`) | | <figure class="highlight"> ``` %f ``` </figure> | Fraction of second (6 digits for printing: `000000` .. `999000`; 1 - 9 digits for parsing: `0` .. `999999999`) (Timestamp is truncated to milliseconds.) | | <figure class="highlight"> ``` %H ``` </figure> | Hour (`00` .. `23`) | | <figure class="highlight"> ``` %h ``` </figure> | Hour (`01` .. `12`) | | <figure class="highlight"> ``` %I ``` </figure> | Hour (`01` .. `12`) | | <figure class="highlight"> ``` %i ``` </figure> | Minutes, numeric (`00` .. `59`) | | <figure class="highlight"> ``` %j ``` </figure> | Day of year (`001` .. `366`) | | <figure class="highlight"> ``` %k ``` </figure> | Hour (`0` .. `23`) | | <figure class="highlight"> ``` %l ``` </figure> | Hour (`1` .. `12`) | | <figure class="highlight"> ``` %M ``` </figure> | Month name (`January` .. `December`) | | <figure class="highlight"> ``` %m ``` </figure> | Month, numeric (`01` .. `12`) | | <figure class="highlight"> ``` %p ``` </figure> | `AM` or `PM` | | <figure class="highlight"> ``` %r ``` </figure> | Time, 12-hour (`hh:mm:ss` followed by `AM` or `PM`) | | <figure class="highlight"> ``` %S ``` </figure> | Seconds (`00` .. `59`) | | <figure class="highlight"> ``` %s ``` </figure> | Seconds (`00` .. `59`) | | <figure class="highlight"> ``` %T ``` </figure> | Time, 24-hour (`hh:mm:ss`) | | <figure class="highlight"> ``` %U ``` </figure> | Week (`00` .. `53`), where Sunday is the first day of the week | | <figure class="highlight"> ``` %u ``` </figure> | Week (`00` .. `53`), where Monday is the first day of the week | | <figure class="highlight"> ``` %V ``` </figure> | Week (`01` .. `53`), where Sunday is the first day of the week; used with `%X` | | <figure class="highlight"> ``` %v ``` </figure> | Week (`01` .. `53`), where Monday is the first day of the week; used with `%x` | | <figure class="highlight"> ``` %W ``` </figure> | Weekday name (`Sunday` .. `Saturday`) | | <figure class="highlight"> ``` %w ``` </figure> | Day of the week (`0` .. `6`), where Sunday is the first day of the week | | <figure class="highlight"> ``` %X ``` </figure> | Year for the week where Sunday is the first day of the week, numeric, four digits; used with `%V` | | <figure class="highlight"> ``` %x ``` </figure> | Year for the week, where Monday is the first day of the week, numeric, four digits; used with `%v` | | <figure class="highlight"> ``` %Y ``` </figure> | Year, numeric, four digits | | <figure class="highlight"> ``` %y ``` </figure> | Year, numeric (two digits) | | <figure class="highlight"> ``` %% ``` </figure> | A literal `%` character | | <figure class="highlight"> ``` %x ``` </figure> | `x`, for any `x` not listed above | ## 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)_ |