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.
The scalar functions take zero, one or more values as the input and return a single value as the result.
<tr>
<td>
{% highlight text %}
value1 <> value2 {% endhighlight %} Returns TRUE if value1 is not equal to value2; returns UNKNOWN if value1 or value2 is NULL.
<tr>
<td>
{% highlight text %}
value1 > value2 {% endhighlight %} Returns TRUE if value1 is greater than value2; returns UNKNOWN if value1 or value2 is NULL.
<tr>
<td>
{% highlight text %}
value1 >= value2 {% endhighlight %} Returns TRUE if value1 is greater than or equal to value2; returns UNKNOWN if value1 or value2 is NULL.
<tr>
<td>
{% highlight text %}
value1 < value2 {% endhighlight %} Returns TRUE if value1 is less than value2; returns UNKNOWN if value1 or value2 is NULL.
<tr>
<td>
{% highlight text %}
value1 <= value2 {% endhighlight %} Returns TRUE if value1 is less than or equal to value2; returns UNKNOWN if value1 or value2 is NULL.
<tr>
<td>
{% highlight text %}
value IS NULL {% endhighlight %} Returns TRUE if value is NULL.
<tr>
<td>
{% highlight text %}
value IS NOT NULL {% endhighlight %} Returns TRUE if value is not NULL.
<tr>
<td>
{% highlight text %}
value1 IS DISTINCT FROM value2 {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
value1 IS NOT DISTINCT FROM value2 {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
value1 BETWEEN [ ASYMMETRIC | SYMMETRIC ] value2 AND value3 {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
value1 NOT BETWEEN [ ASYMMETRIC | SYMMETRIC ] value2 AND value3 {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
string1 LIKE string2 [ ESCAPE char ] {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
string1 NOT LIKE string2 [ ESCAPE char ] {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
string1 SIMILAR TO string2 [ ESCAPE char ] {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
string1 NOT SIMILAR TO string2 [ ESCAPE char ] {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
value1 IN (value2 [, value3]* ) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
value1 NOT IN (value2 [, value3]* ) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
EXISTS (sub-query) {% endhighlight %} 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.
<tr> <td>
{% highlight text %} value IN (sub-query) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
value NOT IN (sub-query) {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
ANY1 !== ANY2 {% endhighlight %} Returns TRUE if ANY1 is not equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
<tr>
<td>
{% highlight java %}
ANY1 > ANY2 {% endhighlight %} Returns TRUE if ANY1 is greater than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
<tr>
<td>
{% highlight java %}
ANY1 >= ANY2 {% endhighlight %} Returns TRUE if ANY1 is greater than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
<tr>
<td>
{% highlight java %}
ANY1 < ANY2 {% endhighlight %} Returns TRUE if ANY1 is less than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
<tr>
<td>
{% highlight java %}
ANY1 <= ANY2 {% endhighlight %} Returns TRUE if ANY1 is less than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
<tr>
<td>
{% highlight java %}
ANY.isNull {% endhighlight %} Returns TRUE if ANY is NULL.
<tr>
<td>
{% highlight java %}
ANY.isNotNull {% endhighlight %} Returns TRUE if ANY is not NULL.
<tr>
<td>
{% highlight java %}
STRING1.like(STRING2) {% endhighlight %} Returns TRUE if STRING1 matches pattern STRING2; returns UNKNOWN if STRING1 or STRING2 is NULL. E.g., “JoKn”.like(“Jo_n%”) returns TRUE.
<tr>
<td>
{% highlight java %}
STRING.similar(STRING) {% endhighlight %} Returns TRUE if STRING1 matches SQL regular expression STRING2; returns UNKNOWN if STRING1 or STRING2 is NULL. E.g., “A”.similar(“A+”) returns TRUE.
<tr>
<td>
{% highlight java %}
ANY1.in(ANY2, ANY3, ...) {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
ANY.in(TABLE) {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
ANY1.between(ANY2, ANY3) {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
ANY1.notBetween(ANY2, ANY3) {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
ANY1 !== ANY2 {% endhighlight %} Returns TRUE if ANY1 is not equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
<tr>
<td>
{% highlight scala %}
ANY1 > ANY2 {% endhighlight %} Returns TRUE if ANY1 is greater than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
<tr>
<td>
{% highlight scala %}
ANY1 >= ANY2 {% endhighlight %} Returns TRUE if ANY1 is greater than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
<tr>
<td>
{% highlight scala %}
ANY1 < ANY2 {% endhighlight %} Returns TRUE if ANY1 is less than ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
<tr>
<td>
{% highlight scala %}
ANY1 <= ANY2 {% endhighlight %} Returns TRUE if ANY1 is less than or equal to ANY2; returns UNKNOWN if ANY1 or ANY2 is NULL.
<tr>
<td>
{% highlight scala %}
ANY.isNull {% endhighlight %} Returns TRUE if ANY is NULL.
<tr>
<td>
{% highlight scala %}
ANY.isNotNull {% endhighlight %} Returns TRUE if ANY is not NULL.
<tr>
<td>
{% highlight scala %}
STRING1.like(STRING2) {% endhighlight %} Returns TRUE if STRING1 matches pattern STRING2; returns UNKNOWN if STRING1 or STRING2 is NULL. E.g., “JoKn”.like(“Jo_n%”) returns TRUE.
<tr>
<td>
{% highlight scala %}
STRING1.similar(STRING2) {% endhighlight %} Returns TRUE if STRING1 matches SQL regular expression STRING2; returns UNKNOWN if STRING1 or STRING2 is NULL. E.g., “A”.similar(“A+”) returns TRUE.
<tr>
<td>
{% highlight scala %}
ANY1.in(ANY2, ANY3, ...) {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
ANY.in(TABLE) {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
ANY1.between(ANY2, ANY3) {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
ANY1.notBetween(ANY2, ANY3) {% endhighlight %} 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.
{% top %}
<tr>
<td>
{% highlight text %}
boolean1 AND boolean2 {% endhighlight %} Returns TRUE if boolean1 and boolean2 are both TRUE. Supports three-valued logic. E.g., TRUE AND UNKNOWN returns UNKNOWN.
<tr>
<td>
{% highlight text %}
NOT boolean {% endhighlight %} Returns TRUE if boolean is FALSE; returns FALSE if boolean is TRUE; returns UNKNOWN if boolean is UNKNOWN.
<tr>
<td>
{% highlight text %}
boolean IS FALSE {% endhighlight %} Returns TRUE if boolean is FALSE; returns FALSE if boolean is TRUE or UNKNOWN.
<tr>
<td>
{% highlight text %}
boolean IS NOT FALSE {% endhighlight %} Returns TRUE if boolean is TRUE or UNKNOWN; returns FALSE if boolean is FALSE.
<tr>
<td>
{% highlight text %}
boolean IS TRUE {% endhighlight %} Returns TRUE if boolean is TRUE; returns FALSE if boolean is FALSE or UNKNOWN.
<tr>
<td>
{% highlight text %}
boolean IS NOT TRUE {% endhighlight %} Returns TRUE if boolean is FALSE or UNKNOWN; returns FALSE if boolean is FALSE.
<tr>
<td>
{% highlight text %}
boolean IS UNKNOWN {% endhighlight %} Returns TRUE if boolean is UNKNOWN; returns FALSE if boolean is TRUE or FALSE.
<tr>
<td>
{% highlight text %}
boolean IS NOT UNKNOWN {% endhighlight %} Returns TRUE if boolean is TRUE or FALSE; returns FALSE if boolean is UNKNOWN.
<tr>
<td>
{% highlight java %}
BOOLEAN1 && BOOLEAN2 {% endhighlight %} Returns TRUE if BOOLEAN1 and BOOLEAN2 are both TRUE. Supports three-valued logic. E.g., true && Null(BOOLEAN) returns UNKNOWN.
<tr>
<td>
{% highlight java %}
!BOOLEAN {% endhighlight %} Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE; returns UNKNOWN if BOOLEAN is UNKNOWN.
<tr>
<td>
{% highlight java %}
BOOLEAN.isTrue {% endhighlight %} Returns TRUE if BOOLEAN is TRUE; returns FALSE if BOOLEAN is FALSE or UNKNOWN.
<tr>
<td>
{% highlight java %}
BOOLEAN.isFalse {% endhighlight %} Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE or UNKNOWN.
<tr>
<td>
{% highlight java %}
BOOLEAN.isNotTrue {% endhighlight %} Returns TRUE if BOOLEAN is FALSE or UNKNOWN; returns FALSE if BOOLEAN is FALSE.
<tr>
<td>
{% highlight java %}
BOOLEAN.isNotFalse {% endhighlight %} Returns TRUE if BOOLEAN is TRUE or UNKNOWN; returns FALSE if BOOLEAN is FALSE.
<tr>
<td>
{% highlight scala %}
BOOLEAN1 && BOOLEAN2 {% endhighlight %} Returns TRUE if BOOLEAN1 and BOOLEAN2 are both TRUE. Supports three-valued logic. E.g., true && Null(Types.BOOLEAN) returns UNKNOWN.
<tr>
<td>
{% highlight scala %}
!BOOLEAN {% endhighlight %} Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE; returns UNKNOWN if BOOLEAN is UNKNOWN.
<tr>
<td>
{% highlight scala %}
BOOLEAN.isTrue {% endhighlight %} Returns TRUE if BOOLEAN is TRUE; returns FALSE if BOOLEAN is FALSE or UNKNOWN.
<tr>
<td>
{% highlight scala %}
BOOLEAN.isFalse {% endhighlight %} Returns TRUE if BOOLEAN is FALSE; returns FALSE if BOOLEAN is TRUE or UNKNOWN.
<tr>
<td>
{% highlight scala %}
BOOLEAN.isNotTrue {% endhighlight %} Returns TRUE if BOOLEAN is FALSE or UNKNOWN; returns FALSE if BOOLEAN is FALSE.
<tr>
<td>
{% highlight scala %}
BOOLEAN.isNotFalse {% endhighlight %} Returns TRUE if BOOLEAN is TRUE or UNKNOWN; returns FALSE if BOOLEAN is FALSE.
{% top %}
<tr>
<td>
{% highlight text %}
numeric1 + numeric2 {% endhighlight %} Returns numeric1 plus numeric2.
<tr>
<td>
{% highlight text %}
numeric1 - numeric2 {% endhighlight %} Returns numeric1 minus numeric2.
<tr>
<td>
{% highlight text %}
numeric1 * numeric2 {% endhighlight %} Returns numeric1 multiplied by numeric2.
<tr>
<td>
{% highlight text %}
numeric1 / numeric2 {% endhighlight %} Returns numeric1 divided by numeric2.
<tr>
<td>
{% highlight text %}
POWER(numeric1, numeric2) {% endhighlight %} Returns numeric1 raised to the power of numeric2.
<tr>
<td>
{% highlight text %}
ABS(numeric) {% endhighlight %} Returns the absolute value of numeric.
<tr>
<td>
{% highlight text %}
MOD(numeric1, numeric2) {% endhighlight %} Returns the remainder (modulus) of numeric1 divided by numeric2. The result is negative only if numeric1 is negative.
<tr>
<td>
{% highlight text %}
SQRT(numeric) {% endhighlight %} Returns the square root of numeric.
<tr>
<td>
{% highlight text %}
LN(numeric) {% endhighlight %} Returns the natural logarithm (base e) of numeric.
<tr>
<td>
{% highlight text %}
LOG10(numeric) {% endhighlight %} Returns the base 10 logarithm of numeric.
<tr>
<td>
{% highlight text %}
LOG2(numeric) {% endhighlight %} Returns the base 2 logarithm of numeric.
<tr>
<td>
{% highlight text %}
LOG(numeric2) LOG(numeric1, numeric2) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
EXP(numeric) {% endhighlight %} Returns e raised to the power of numeric.
<tr>
<td>
{% highlight text %}
CEIL(numeric) CEILING(numeric) {% endhighlight %} Rounds numeric up, and returns the smallest number that is greater than or equal to numeric.
<tr>
<td>
{% highlight text %}
FLOOR(numeric) {% endhighlight %} Rounds numeric down, and returns the largest number that is less than or equal to numeric.
<tr>
<td>
{% highlight text %}
SIN(numeric) {% endhighlight %} Returns the sine of numeric.
<tr>
<td>
{% highlight text %}
SINH(numeric) {% endhighlight %} Returns the hyperbolic sine of numeric. The return type is DOUBLE.
<tr>
<td>
{% highlight text %}
COS(numeric) {% endhighlight %} Returns the cosine of numeric.
<tr>
<td>
{% highlight text %}
TAN(numeric) {% endhighlight %} Returns the tangent of numeric.
<tr>
<td>
{% highlight text %}
TANH(numeric) {% endhighlight %} Returns the hyperbolic tangent of numeric. The return type is DOUBLE.
<tr>
<td>
{% highlight text %}
COT(numeric) {% endhighlight %} Returns the cotangent of a numeric.
<tr>
<td>
{% highlight text %}
ASIN(numeric) {% endhighlight %} Returns the arc sine of numeric.
<tr>
<td>
{% highlight text %}
ACOS(numeric) {% endhighlight %} Returns the arc cosine of numeric.
<tr>
<td>
{% highlight text %}
ATAN(numeric) {% endhighlight %} Returns the arc tangent of numeric.
<tr>
<td>
{% highlight text %}
ATAN2(numeric1, numeric2) {% endhighlight %} Returns the arc tangent of a coordinate (numeric1, numeric2).
<tr>
<td>
{% highlight text %}
COSH(numeric) {% endhighlight %} Returns the hyperbolic cosine of NUMERIC. Return value type is DOUBLE.
<tr>
<td>
{% highlight text %}
DEGREES(numeric) {% endhighlight %} Returns the degree representation of a radian numeric.
<tr>
<td>
{% highlight text %}
RADIANS(numeric) {% endhighlight %} Returns the radian representation of a degree numeric.
<tr>
<td>
{% highlight text %}
SIGN(numeric) {% endhighlight %} Returns the signum of numeric.
<tr>
<td>
{% highlight text %}
ROUND(numeric, integer) {% endhighlight %} Returns a number rounded to integer decimal places for numeric.
<tr>
<td>
{% highlight text %}
PI {% endhighlight %} Returns a value that is closer than any other values to pi. {% highlight text %} E() {% endhighlight %} Returns a value that is closer than any other values to e.
<tr>
<td>
{% highlight text %}
RAND() {% endhighlight %} Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).
<tr>
<td>
{% highlight text %}
RAND(integer) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
RAND_INTEGER(integer) {% endhighlight %} Returns a pseudorandom integer value between 0 (inclusive) and integer (exclusive).
<tr>
<td>
{% highlight text %}
RAND_INTEGER(integer1, integer2) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
UUID() {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
BIN(integer) {% endhighlight %} 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’.
<tr> <td>
{% highlight text %} HEX(numeric) HEX(string) {% endhighlight %} 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”.
<tr>
<td>
{% highlight java %}
NUMERIC1 + NUMERIC2 {% endhighlight %} Returns NUMERIC1 plus NUMERIC2.
<tr>
<td>
{% highlight java %}
NUMERIC1 - NUMERIC2 {% endhighlight %} Returns NUMERIC1 minus NUMERIC2.
<tr>
<td>
{% highlight java %}
NUMERIC1 * NUMERIC2 {% endhighlight %} Returns NUMERIC1 multiplied by NUMERIC2.
<tr>
<td>
{% highlight java %}
NUMERIC1 / NUMERIC2 {% endhighlight %} Returns NUMERIC1 divided by NUMERIC2.
<tr>
<td>
{% highlight java %}
NUMERIC1.power(NUMERIC2) {% endhighlight %} Returns NUMERIC1 raised to the power of NUMERIC2.
<tr>
<td>
{% highlight java %}
NUMERIC.abs() {% endhighlight %} Returns the absolute value of NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC1 % NUMERIC2 {% endhighlight %} Returns the remainder (modulus) of NUMERIC1 divided by NUMERIC2. The result is negative only if numeric1 is negative.
<tr>
<td>
{% highlight java %}
NUMERIC.sqrt() {% endhighlight %} Returns the square root of NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.ln() {% endhighlight %} Returns the natural logarithm (base e) of NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.log10() {% endhighlight %} Returns the base 10 logarithm of NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.log2() {% endhighlight %} Returns the base 2 logarithm of NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC1.log() NUMERIC1.log(NUMERIC2) {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
NUMERIC.exp() {% endhighlight %} Returns e raised to the power of NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.ceil() {% endhighlight %} Rounds NUMERIC up, and returns the smallest number that is greater than or equal to NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.floor() {% endhighlight %} Rounds NUMERIC down, and returns the largest number that is less than or equal to NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.sin() {% endhighlight %} Returns the sine of NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.sinh() {% endhighlight %} Returns the hyperbolic sine of NUMERIC. The return type is DOUBLE.
<tr>
<td>
{% highlight java %}
NUMERIC.cos() {% endhighlight %} Returns the cosine of NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.tan() {% endhighlight %} Returns the tangent of NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.tanh() {% endhighlight %} Returns the hyperbolic tangent of NUMERIC. The return type is DOUBLE.
<tr>
<td>
{% highlight java %}
NUMERIC.cot() {% endhighlight %} Returns the cotangent of a NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.asin() {% endhighlight %} Returns the arc sine of NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.acos() {% endhighlight %} Returns the arc cosine of NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.atan() {% endhighlight %} Returns the arc tangent of NUMERIC.
<tr>
<td>
{% highlight java %}
atan2(NUMERIC1, NUMERIC2) {% endhighlight %} Returns the arc tangent of a coordinate (NUMERIC1, NUMERIC2).
<tr>
<td>
{% highlight java %}
NUMERIC.cosh() {% endhighlight %} Returns the hyperbolic cosine of NUMERIC. Return value type is DOUBLE.
<tr>
<td>
{% highlight java %}
NUMERIC.degrees() {% endhighlight %} Returns the degree representation of a radian NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.radians() {% endhighlight %} Returns the radian representation of a degree NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.sign() {% endhighlight %} Returns the signum of NUMERIC.
<tr>
<td>
{% highlight java %}
NUMERIC.round(INT) {% endhighlight %} Returns a number rounded to INT decimal places for NUMERIC.
<tr>
<td>
{% highlight java %}
pi() {% endhighlight %} Returns a value that is closer than any other values to pi.
<tr>
<td>
{% highlight java %}
e() {% endhighlight %} Returns a value that is closer than any other values to e.
<tr>
<td>
{% highlight java %}
rand() {% endhighlight %} Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).
<tr>
<td>
{% highlight java %}
rand(INTEGER) {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
randInteger(INTEGER) {% endhighlight %} Returns a pseudorandom integer value between 0 (inclusive) and INTEGER (exclusive).
<tr>
<td>
{% highlight java %}
randInteger(INTEGER1, INTEGER2) {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
uuid() {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
INTEGER.bin() {% endhighlight %} 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”.
<tr>
<td>
{% highlight java %}
NUMERIC.hex() STRING.hex() {% endhighlight %} 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”.
<tr>
<td>
{% highlight scala %}
NUMERIC1 + NUMERIC2 {% endhighlight %} Returns NUMERIC1 plus NUMERIC2.
<tr>
<td>
{% highlight scala %}
NUMERIC1 - NUMERIC2 {% endhighlight %} Returns NUMERIC1 minus NUMERIC2.
<tr>
<td>
{% highlight scala %}
NUMERIC1 * NUMERIC2 {% endhighlight %} Returns NUMERIC1 multiplied by NUMERIC2.
<tr>
<td>
{% highlight scala %}
NUMERIC1 / NUMERIC2 {% endhighlight %} Returns NUMERIC1 divided by NUMERIC2.
<tr>
<td>
{% highlight scala %}
NUMERIC1.power(NUMERIC2) {% endhighlight %} Returns NUMERIC1 raised to the power of NUMERIC2.
<tr>
<td>
{% highlight scala %}
NUMERIC.abs() {% endhighlight %} Returns the absolute value of NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC1 % NUMERIC2 {% endhighlight %} Returns the remainder (modulus) of NUMERIC1 divided by NUMERIC2. The result is negative only if numeric1 is negative.
<tr>
<td>
{% highlight scala %}
NUMERIC.sqrt() {% endhighlight %} Returns the square root of NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.ln() {% endhighlight %} Returns the natural logarithm (base e) of NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.log10() {% endhighlight %} Returns the base 10 logarithm of NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.log2() {% endhighlight %} Returns the base 2 logarithm of NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC1.log() NUMERIC1.log(NUMERIC2) {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
NUMERIC.exp() {% endhighlight %} Returns e raised to the power of NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.ceil() {% endhighlight %} Rounds NUMERIC up, and returns the smallest number that is greater than or equal to NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.floor() {% endhighlight %} Rounds NUMERIC down, and returns the largest number that is less than or equal to NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.sin() {% endhighlight %} Returns the sine of NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.sinh() {% endhighlight %} Returns the hyperbolic sine of NUMERIC. The return type is DOUBLE.
<tr>
<td>
{% highlight scala %}
NUMERIC.cos() {% endhighlight %} Returns the cosine of NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.tan() {% endhighlight %} Returns the tangent of NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.tanh() {% endhighlight %} Returns the hyperbolic tangent of NUMERIC. The return type is DOUBLE.
<tr>
<td>
{% highlight scala %}
NUMERIC.cot() {% endhighlight %} Returns the cotangent of a NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.asin() {% endhighlight %} Returns the arc sine of NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.acos() {% endhighlight %} Returns the arc cosine of NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.atan() {% endhighlight %} Returns the arc tangent of NUMERIC.
<tr>
<td>
{% highlight scala %}
atan2(NUMERIC1, NUMERIC2) {% endhighlight %} Returns the arc tangent of a coordinate (NUMERIC1, NUMERIC2).
<tr>
<td>
{% highlight scala %}
NUMERIC.cosh() {% endhighlight %} Returns the hyperbolic cosine of NUMERIC. Return value type is DOUBLE.
<tr>
<td>
{% highlight scala %}
NUMERIC.degrees() {% endhighlight %} Returns the degree representation of a radian NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.radians() {% endhighlight %} Returns the radian representation of a degree NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.sign() {% endhighlight %} Returns the signum of NUMERIC.
<tr>
<td>
{% highlight scala %}
NUMERIC.round(INT) {% endhighlight %} Returns a number rounded to INT decimal places for NUMERIC.
<tr>
<td>
{% highlight scala %}
pi() {% endhighlight %} Returns a value that is closer than any other values to pi.
<tr>
<td>
{% highlight scala %}
e() {% endhighlight %} Returns a value that is closer than any other values to e.
<tr>
<td>
{% highlight scala %}
rand() {% endhighlight %} Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).
<tr>
<td>
{% highlight scala %}
rand(INTEGER) {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
randInteger(INTEGER) {% endhighlight %} Returns a pseudorandom integer value between 0 (inclusive) and INTEGER (exclusive).
<tr>
<td>
{% highlight scala %}
randInteger(INTEGER1, INTEGER2) {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
uuid() {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
INTEGER.bin() {% endhighlight %} 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”.
<tr>
<td>
{% highlight scala %}
NUMERIC.hex() STRING.hex() {% endhighlight %} 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”.
{% top %}
<tr>
<td>
{% highlight text %}
CHAR_LENGTH(string) CHARACTER_LENGTH(string) {% endhighlight %} Returns the number of characters in string.
<tr>
<td>
{% highlight text %}
UPPER(string) {% endhighlight %} Returns string in uppercase.
<tr>
<td>
{% highlight text %}
LOWER(string) {% endhighlight %} Returns string in lowercase.
<tr>
<td>
{% highlight text %}
POSITION(string1 IN string2) {% endhighlight %} Returns the position (start from 1) of the first occurrence of string1 in string2; returns 0 if string1 cannot be found in string2.
<tr>
<td>
{% highlight text %}
TRIM([ BOTH | LEADING | TRAILING ] string1 FROM string2) {% endhighlight %} Returns a string that removes leading and/or trailing characters string1 from string2. By default, whitespaces at both sides are removed.
<tr>
<td>
{% highlight text %}
LTRIM(string) {% endhighlight %} Returns a string that removes the left whitespaces from string. E.g., LTRIM(' This is a test String.') returns “This is a test String.”.
<tr>
<td>
{% highlight text %}
RTRIM(string) {% endhighlight %} Returns a string that removes the right whitespaces from string. E.g., RTRIM('This is a test String. ') returns “This is a test String.”.
<tr>
<td>
{% highlight text %}
REPEAT(string, integer) {% endhighlight %} 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.”.
<tr>
<td>
{% highlight text %}
REGEXP_REPLACE(string1, string2, string3) {% endhighlight %} 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”.
<tr>
<td>
{% highlight text %}
OVERLAY(string1 PLACING string2 FROM integer1 [ FOR integer2 ]) {% endhighlight %} 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”
<tr>
<td>
{% highlight text %}
SUBSTRING(string FROM integer1 [ FOR integer2 ]) {% endhighlight %} Returns a substring of string starting from position integer1 with length integer2 (to the end by default).
<tr>
<td>
{% highlight text %}
REPLACE(string1, string2, string3) {% endhighlight %} 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”.
<tr>
<td>
{% highlight text %}
REGEXP_EXTRACT(string1, string2[, integer]) {% endhighlight %} 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”.
<tr>
<td>
{% highlight text %}
INITCAP(string) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
CONCAT(string1, string2,...) {% endhighlight %} Returns a string that concatenates string1, string2, .... Returns NULL if any argument is NULL. E.g., CONCAT(‘AA’, ‘BB’, ‘CC’) returns “AABBCC”.
<tr>
<td>
{% highlight text %}
CONCAT_WS(string1, string2, string3,...) {% endhighlight %} 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”.
<tr>
<td>
{% highlight text %}
LPAD(string1, integer, string2) {% endhighlight %} 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”.
<tr>
<td>
{% highlight text %}
RPAD(string1, integer, string2) {% endhighlight %} 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”.
<tr>
<td>
{% highlight text %}
FROM_BASE64(string) {% endhighlight %} Returns the base64-decoded result from string; returns NULL if string is NULL. E.g., FROM_BASE64(‘aGVsbG8gd29ybGQ=’) returns “hello world”.
<tr>
<td>
{% highlight text %}
TO_BASE64(string) {% endhighlight %} Returns the base64-encoded result from string; returns NULL if string is NULL. E.g., TO_BASE64(‘hello world’) returns “aGVsbG8gd29ybGQ=”.
<tr>
<td>
{% highlight java %}
STRING.charLength() {% endhighlight %} Returns the number of characters in STRING.
<tr>
<td>
{% highlight java %}
STRING.upperCase() {% endhighlight %} Returns STRING in uppercase.
<tr>
<td>
{% highlight java %}
STRING.lowerCase() {% endhighlight %} Returns STRING in lowercase.
<tr>
<td>
{% highlight java %}
STRING1.position(STRING2) {% endhighlight %} Returns the position (start from 1) of the first occurrence of STRING1 in STRING2; returns 0 if STRING1 cannot be found in STRING2.
<tr>
<td>
{% highlight java %}
STRING1.trim(LEADING, STRING2) STRING1.trim(TRAILING, STRING2) STRING1.trim(BOTH, STRING2) STRING1.trim(BOTH) STRING1.trim() {% endhighlight %} Returns a string that removes leading and/or trailing characters STRING2 from STRING1. By default, whitespaces at both sides are removed.
<tr>
<td>
{% highlight java %}
STRING.ltrim() {% endhighlight %} Returns a string that removes the left whitespaces from STRING. E.g., ' This is a test String.'.ltrim() returns “This is a test String.”.
<tr>
<td>
{% highlight java %}
STRING.rtrim() {% endhighlight %} Returns a string that removes the right whitespaces from STRING. E.g., 'This is a test String. '.rtrim() returns “This is a test String.”.
<tr>
<td>
{% highlight java %}
STRING.repeat(INT) {% endhighlight %} 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.”.
<tr>
<td>
{% highlight java %}
STRING1.regexpReplace(STRING2, STRING3) {% endhighlight %} 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”.
<tr>
<td>
{% highlight java %}
STRING1.overlay(STRING2, INT1) STRING1.overlay(STRING2, INT1, INT2) {% endhighlight %} 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”.
<tr>
<td>
{% highlight java %}
STRING.substring(INT1) STRING.substring(INT1, INT2) {% endhighlight %} Returns a substring of STRING starting from position INT1 with length INT2 (to the end by default).
<tr>
<td>
{% highlight java %}
STRING1.replace(STRING2, STRING3) {% endhighlight %} 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’.
<tr>
<td>
{% highlight java %}
STRING1.regexpExtract(STRING2[, INTEGER1]) {% endhighlight %} 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”.
<tr>
<td>
{% highlight java %}
STRING.initCap() {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
concat(STRING1, STRING2, ...) {% endhighlight %} Returns a string that concatenates STRING1, STRING2, .... Returns NULL if any argument is NULL. E.g., concat(‘AA’, ‘BB’, ‘CC’) returns “AABBCC”.
<tr>
<td>
{% highlight java %}
concat_ws(STRING1, STRING2, STRING3, ...) {% endhighlight %} 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”.
<tr>
<td>
{% highlight java %}
STRING1.lpad(INT, STRING2) {% endhighlight %} 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”.
<tr>
<td>
{% highlight java %}
STRING1.rpad(INT, STRING2) {% endhighlight %} 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”.
<tr>
<td>
{% highlight java %}
STRING.fromBase64() {% endhighlight %} Returns the base64-decoded result from STRING; returns NULL if STRING is NULL. E.g., ‘aGVsbG8gd29ybGQ=’.fromBase64() returns “hello world”.
<tr>
<td>
{% highlight java %}
STRING.toBase64() {% endhighlight %} Returns the base64-encoded result from STRING; returns NULL if STRING is NULL. E.g., ‘hello world’.toBase64() returns “aGVsbG8gd29ybGQ=”.
<tr>
<td>
{% highlight scala %}
STRING.charLength() {% endhighlight %} Returns the number of characters in STRING.
<tr>
<td>
{% highlight scala %}
STRING.upperCase() {% endhighlight %} Returns STRING in uppercase.
<tr>
<td>
{% highlight scala %}
STRING.lowerCase() {% endhighlight %} Returns STRING in lowercase.
<tr>
<td>
{% highlight scala %}
STRING1.position(STRING2) {% endhighlight %} Returns the position (start from 1) of the first occurrence of STRING1 in STRING2; returns 0 if STRING1 cannot be found in STRING2.
<tr>
<td>
{% highlight scala %}
STRING.trim( leading = true, trailing = true, character = " ") {% endhighlight %} Returns a string that removes leading and/or trailing characters from STRING.
<tr>
<td>
{% highlight scala %}
STRING.ltrim() {% endhighlight %} Returns a string that removes the left whitespaces from STRING. E.g., " This is a test String.".ltrim() returns “This is a test String.”.
<tr>
<td>
{% highlight scala %}
STRING.rtrim() {% endhighlight %} Returns a string that removes the right whitespaces from STRING. E.g., "This is a test String. ".rtrim() returns “This is a test String.”.
<tr>
<td>
{% highlight scala %}
STRING.repeat(INT) {% endhighlight %} 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.”.
<tr>
<td>
{% highlight scala %}
STRING1.regexpReplace(STRING2, STRING3) {% endhighlight %} 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”.
<tr>
<td>
{% highlight scala %}
STRING1.overlay(STRING2, INT1) STRING1.overlay(STRING2, INT1, INT2) {% endhighlight %} 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”.
<tr>
<td>
{% highlight scala %}
STRING.substring(INT1) STRING.substring(INT1, INT2) {% endhighlight %} Returns a substring of STRING starting from position INT1 with length INT2 (to the end by default).
<tr>
<td>
{% highlight scala %}
STRING1.replace(STRING2, STRING3) {% endhighlight %} 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”.
<tr>
<td>
{% highlight scala %}
STRING1.regexpExtract(STRING2[, INTEGER1]) {% endhighlight %} 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”.
<tr>
<td>
{% highlight scala %}
STRING.initCap() {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
concat(STRING1, STRING2, ...) {% endhighlight %} Returns a string that concatenates STRING1, STRING2, .... Returns NULL if any argument is NULL. E.g., concat(“AA”, “BB”, “CC”) returns “AABBCC”.
<tr>
<td>
{% highlight scala %}
concat_ws(STRING1, STRING2, STRING3, ...) {% endhighlight %} 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”.
<tr>
<td>
{% highlight scala %}
STRING1.lpad(INT, STRING2) {% endhighlight %} 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”.
<tr>
<td>
{% highlight scala %}
STRING1.rpad(INT, STRING2) {% endhighlight %} 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”.
<tr>
<td>
{% highlight scala %}
STRING.fromBase64() {% endhighlight %} Returns the base64-decoded result from STRING; returns null If STRING is NULL. E.g., “aGVsbG8gd29ybGQ=”.fromBase64() returns “hello world”.
<tr>
<td>
{% highlight scala %}
STRING.toBase64() {% endhighlight %} Returns the base64-encoded result from STRING; returns NULL if STRING is NULL. E.g., “hello world”.toBase64() returns “aGVsbG8gd29ybGQ=”.
{% top %}
<tr>
<td>
{% highlight text %}
TIME string {% endhighlight %} Returns a SQL time parsed from string in form of “HH:mm:ss”.
<tr>
<td>
{% highlight text %}
TIMESTAMP string {% endhighlight %} Returns a SQL timestamp parsed from string in form of “yyyy-MM-dd HH:mm:ss[.SSS]”.
<tr>
<td>
{% highlight text %}
INTERVAL string range {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
CURRENT_DATE {% endhighlight %} Returns the current SQL date in the UTC time zone.
<tr>
<td>
{% highlight text %}
CURRENT_TIME {% endhighlight %} Returns the current SQL time in the UTC time zone.
<tr>
<td>
{% highlight text %}
CURRENT_TIMESTAMP {% endhighlight %} Returns the current SQL timestamp in the UTC time zone.
<tr>
<td>
{% highlight text %}
LOCALTIME {% endhighlight %} Returns the current SQL time in local time zone.
<tr>
<td>
{% highlight text %}
LOCALTIMESTAMP {% endhighlight %} Returns the current SQL timestamp in local time zone.
<tr>
<td>
{% highlight text %}
EXTRACT(timeintervalunit FROM temporal) {% endhighlight %} Returns a long value extracted from the timeintervalunit part of temporal. E.g., EXTRACT(DAY FROM DATE ‘2006-06-05’) returns 5.
<tr>
<td>
{% highlight text %}
YEAR(date) {% endhighlight %} Returns the year from SQL date date. Equivalent to EXTRACT(YEAR FROM date). E.g., YEAR(DATE ‘1994-09-27’) returns 1994.
<tr>
<td>
{% highlight text %}
QUARTER(date) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
MONTH(date) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
WEEK(date) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
DAYOFYEAR(date) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
DAYOFMONTH(date) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
DAYOFWEEK(date) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
HOUR(timestamp) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
MINUTE(timestamp) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
SECOND(timestamp) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
FLOOR(timepoint TO timeintervalunit) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
CEIL(timepoint TO timeintervalunit) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
(timepoint1, temporal1) OVERLAPS (timepoint2, temporal2) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
DATE_FORMAT(timestamp, string) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
TIMESTAMPADD(timeintervalunit, interval, timepoint) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2) {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
STRING.toTime() {% endhighlight %} Returns a SQL time parsed from STRING in form of “HH:mm:ss”.
<tr>
<td>
{% highlight java %}
STRING.toTimestamp() {% endhighlight %} Returns a SQL timestamp parsed from STRING in form of “yyyy-MM-dd HH:mm:ss[.SSS]”.
<tr>
<td>
{% highlight java %}
NUMERIC.year NUMERIC.years {% endhighlight %} Creates an interval of months for NUMERIC years. {% highlight java %} NUMERIC.quarter NUMERIC.quarters {% endhighlight %} Creates an interval of months for NUMERIC quarters. E.g., 2.quarters returns 6. {% highlight java %} NUMERIC.month NUMERIC.months {% endhighlight %} Creates an interval of NUMERIC months.
<tr>
<td>
{% highlight java %}
NUMERIC.week NUMERIC.weeks {% endhighlight %} Creates an interval of milliseconds for NUMERIC weeks. E.g., 2.weeks returns 1209600000.
<tr>
<td>
{% highlight java %}
NUMERIC.day NUMERIC.days {% endhighlight %} Creates an interval of milliseconds for NUMERIC days.
<tr>
<td>
{% highlight java %}
NUMERIC.hour NUMERIC.hours {% endhighlight %} Creates an interval of milliseconds for NUMERIC hours.
<tr>
<td>
{% highlight java %}
NUMERIC.minute NUMERIC.minutes {% endhighlight %} Creates an interval of milliseconds for NUMERIC minutes.
<tr>
<td>
{% highlight java %}
NUMERIC.second NUMERIC.seconds {% endhighlight %} Creates an interval of milliseconds for NUMERIC seconds.
<tr>
<td>
{% highlight java %}
NUMERIC.milli NUMERIC.millis {% endhighlight %} Creates an interval of NUMERIC milliseconds.
<tr>
<td>
{% highlight java %}
currentDate() {% endhighlight %} Returns the current SQL date in the UTC time zone.
<tr>
<td>
{% highlight java %}
currentTime() {% endhighlight %} Returns the current SQL time in the UTC time zone.
<tr>
<td>
{% highlight java %}
currentTimestamp() {% endhighlight %} Returns the current SQL timestamp in the UTC time zone.
<tr>
<td>
{% highlight java %}
localTime() {% endhighlight %} Returns the current SQL time in local time zone.
<tr>
<td>
{% highlight java %}
localTimestamp() {% endhighlight %} Returns the current SQL timestamp in local time zone.
<tr>
<td>
{% highlight java %}
TEMPORAL.extract(TIMEINTERVALUNIT) {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
TIMEPOINT.floor(TIMEINTERVALUNIT) {% endhighlight %} Returns a value that rounds TIMEPOINT down to the time unit TIMEINTERVALUNIT. E.g., ‘12:44:31’.toDate.floor(MINUTE) returns 12:44:00.
<tr>
<td>
{% highlight java %}
TIMEPOINT.ceil(TIMEINTERVALUNIT) {% endhighlight %} Returns a value that rounds TIMEPOINT up to the time unit TIMEINTERVALUNIT. E.g., ‘12:44:31’.toTime.floor(MINUTE) returns 12:45:00.
<tr>
<td>
{% highlight java %}
temporalOverlaps(TIMEPOINT1, TEMPORAL1, TIMEPOINT2, TEMPORAL2) {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
dateFormat(TIMESTAMP, STRING) {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
timestampDiff(TIMEPOINTUNIT, TIMEPOINT1, TIMEPOINT2) {% endhighlight %} 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.
</tbody>
<tr>
<td>
{% highlight scala %}
STRING.toTime {% endhighlight %} Returns a SQL time parsed from STRING in form of “HH:mm:ss”.
<tr>
<td>
{% highlight scala %}
STRING.toTimestamp {% endhighlight %} Returns a SQL timestamp parsed from STRING in form of “yyyy-MM-dd HH:mm:ss[.SSS]”.
<tr>
<td>
{% highlight scala %}
NUMERIC.year NUMERIC.years {% endhighlight %} Creates an interval of months for NUMERIC years.
<tr>
<td>
{% highlight scala %}
NUMERIC.quarter NUMERIC.quarters {% endhighlight %} Creates an interval of months for NUMERIC quarters. E.g., 2.quarters returns 6.
<tr>
<td>
{% highlight scala %}
NUMERIC.month NUMERIC.months {% endhighlight %} Creates an interval of NUMERIC months.
<tr>
<td>
{% highlight scala %}
NUMERIC.week NUMERIC.weeks {% endhighlight %} Creates an interval of milliseconds for NUMERIC weeks. E.g., 2.weeks returns 1209600000.
<tr>
<td>
{% highlight scala %}
NUMERIC.day NUMERIC.days {% endhighlight %} Creates an interval of milliseconds for NUMERIC days.
<tr>
<td>
{% highlight scala %}
NUMERIC.hour NUMERIC.hours {% endhighlight %} Creates an interval of milliseconds for NUMERIC hours.
<tr>
<td>
{% highlight scala %}
NUMERIC.minute NUMERIC.minutes {% endhighlight %} Creates an interval of milliseconds for NUMERIC minutes.
<tr>
<td>
{% highlight scala %}
NUMERIC.second NUMERIC.seconds {% endhighlight %} Creates an interval of milliseconds for NUMERIC seconds.
<tr>
<td>
{% highlight scala %}
NUMERIC.milli NUMERIC.millis {% endhighlight %} Creates an interval of NUMERIC milliseconds.
<tr>
<td>
{% highlight scala %}
currentDate() {% endhighlight %} Returns the current SQL date in the UTC time zone.
<tr>
<td>
{% highlight scala %}
currentTime() {% endhighlight %} Returns the current SQL time in the UTC time zone.
<tr>
<td>
{% highlight scala %}
currentTimestamp() {% endhighlight %} Returns the current SQL timestamp in the UTC time zone.
<tr>
<td>
{% highlight scala %}
localTime() {% endhighlight %} Returns the current SQL time in local time zone.
<tr>
<td>
{% highlight scala %}
localTimestamp() {% endhighlight %} Returns the current SQL timestamp in local time zone.
<tr>
<td>
{% highlight scala %}
TEMPORAL.extract(TIMEINTERVALUNIT) {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
TIMEPOINT.floor(TIMEINTERVALUNIT) {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
TIMEPOINT.ceil(TIMEINTERVALUNIT) {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
temporalOverlaps(TIMEPOINT1, TEMPORAL1, TIMEPOINT2, TEMPORAL2) {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
dateFormat(TIMESTAMP, STRING) {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
timestampDiff(TIMEPOINTUNIT, TIMEPOINT1, TIMEPOINT2) {% endhighlight %} 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.
{% top %}
<tr>
<td>
{% highlight text %}
CASE WHEN condition1 THEN result1 [ WHEN condition2 THEN result2 ]* [ ELSE resultZ ] END {% endhighlight %} Returns resultX when the first conditionX is met. When no condition is met, returns resultZ if it is provided and returns NULL otherwise.
<tr>
<td>
{% highlight text %}
NULLIF(value1, value2) {% endhighlight %} Returns NULL if value1 is equal to value2; returns value1 otherwise. E.g., NULLIF(5, 5) returns NULL; NULLIF(5, 0) returns 5.
<tr>
<td>
{% highlight text %}
COALESCE(value1, value2 [, value3 ]* ) {% endhighlight %} Returns the first value that is not NULL from value1, value2, .... E.g., COALESCE(NULL, 5) returns 5.
{% top %}
{% top %}
<tr>
<td>
{% highlight text %}
array ‘[’ integer ‘]’ {% endhighlight %} Returns the element at position integer in array. The index starts from 1.
<tr>
<td>
{% highlight text %}
ELEMENT(array) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
CARDINALITY(map) {% endhighlight %} Returns the number of entries in map.
<tr>
<td>
{% highlight text %}
map ‘[’ value ‘]’ {% endhighlight %} Returns the value specified by key value in map.
<tr>
<td>
{% highlight java %}
ARRAY.at(INT) {% endhighlight %} Returns the element at position INT in ARRAY. The index starts from 1.
<tr>
<td>
{% highlight java %}
ARRAY.element() {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
MAP.cardinality() {% endhighlight %} Returns the number of entries in MAP.
<tr>
<td>
{% highlight java %}
MAP.at(ANY) {% endhighlight %} Returns the value specified by key ANY in MAP.
<tr>
<td>
{% highlight scala %}
ARRAY.at(INT) {% endhighlight %} Returns the element at position INT in ARRAY. The index starts from 1.
<tr>
<td>
{% highlight scala %}
ARRAY.element() {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
MAP.cardinality() {% endhighlight %} Returns the number of entries in MAP.
<tr>
<td>
{% highlight scala %}
MAP.at(ANY) {% endhighlight %} Returns the value specified by key ANY in MAP.
<tr>
<td>
{% highlight text %}
ARRAY ‘[’ value1 [, value2 ]* ‘]’ {% endhighlight %} Returns an array created from a list of values (value1, value2, ...).
<tr>
<td>
{% highlight text %}
MAP ‘[’ value1, value2 [, value3, value4 ]* ‘]’ {% endhighlight %} Returns a map created from a list of key-value pairs ((value1, value2), (value3, value4), ...).
<tr>
<td>
{% highlight java %}
array(ANY1, ANY2, ...) {% endhighlight %} Returns an array created from a list of object values (ANY1, ANY2, ...).
<tr>
<td>
{% highlight java %}
map(ANY1, ANY2, ANY3, ANY4, ...) {% endhighlight %} Returns a map created from a list of key-value pairs ((ANY1, ANY2), (ANY3, ANY4), ...).
<tr>
<td>
{% highlight java %}
NUMERIC.rows {% endhighlight %} Creates a NUMERIC interval of rows (commonly used in window creation).
<tr>
<td>
{% highlight scala %}
array(ANY1, ANY2, ...) {% endhighlight %} Returns an array created from a list of object values (ANY1, ANY2, ...).
<tr>
<td>
{% highlight scala %}
map(ANY1, ANY2, ANY3, ANY4, ...) {% endhighlight %} Returns a map created from a list of key-value pairs ((ANY1, ANY2), (ANY3, ANY4), ...).
<tr>
<td>
{% highlight scala %}
NUMERIC.rows {% endhighlight %} Creates a NUMERIC interval of rows (commonly used in window creation).
{% top %}
<tr>
<td>
{% highlight text %}
tableName.compositeType.* {% endhighlight %} 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).
<tr>
<td>
{% highlight java %}
ANY.flatten() {% endhighlight %} 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).
<tr>
<td>
{% highlight scala %}
ANY.flatten() {% endhighlight %} 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).
{% top %}
<tr>
<td>
{% highlight text %}
GROUPING(expression1 [, expression2]* ) GROUPING_ID(expression1 [, expression2]* ) {% endhighlight %} Returns a bit vector of the given grouping expressions.
<tr>
<td>
{% highlight text %}
SHA1(string) {% endhighlight %} Returns the SHA-1 hash of string as a string of 40 hexadecimal digits; returns NULL if string is NULL.
<tr>
<td>
{% highlight text %}
SHA224(string) {% endhighlight %} Returns the SHA-224 hash of string as a string of 56 hexadecimal digits; returns NULL if string is NULL.
<tr>
<td>
{% highlight text %}
SHA256(string) {% endhighlight %} Returns the SHA-256 hash of string as a string of 64 hexadecimal digits; returns NULL if string is NULL.
<tr>
<td>
{% highlight text %}
SHA384(string) {% endhighlight %} Returns the SHA-384 hash of string as a string of 96 hexadecimal digits; returns NULL if string is NULL.
<tr>
<td>
{% highlight text %}
SHA512(string) {% endhighlight %} Returns the SHA-512 hash of string as a string of 128 hexadecimal digits; returns NULL if string is NULL.
<tr>
<td>
{% highlight text %}
SHA2(string, hashLength) {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
STRING.sha1() {% endhighlight %} Returns the SHA-1 hash of STRING as a string of 40 hexadecimal digits; returns NULL if STRING is NULL.
<tr>
<td>
{% highlight java %}
STRING.sha224() {% endhighlight %} Returns the SHA-224 hash of STRING as a string of 56 hexadecimal digits; returns NULL if STRING is NULL.
<tr>
<td>
{% highlight java %}
STRING.sha256() {% endhighlight %} Returns the SHA-256 hash of STRING as a string of 64 hexadecimal digits; returns NULL if STRING is NULL.
<tr>
<td>
{% highlight java %}
STRING.sha384() {% endhighlight %} Returns the SHA-384 hash of STRING as a string of 96 hexadecimal digits; returns NULL if STRING is NULL.
<tr>
<td>
{% highlight java %}
STRING.sha512() {% endhighlight %} Returns the SHA-512 hash of STRING as a string of 128 hexadecimal digits; returns NULL if STRING is NULL.
<tr>
<td>
{% highlight java %}
STRING.sha2(INT) {% endhighlight %} 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.
<tr>
<td>
{% highlight scala %}
STRING.sha1() {% endhighlight %} Returns the SHA-1 hash of STRING as a string of 40 hexadecimal digits; returns NULL if STRING is NULL.
<tr>
<td>
{% highlight scala %}
STRING.sha224() {% endhighlight %} Returns the SHA-224 hash of STRING as a string of 56 hexadecimal digits; returns NULL if STRING is NULL.
<tr>
<td>
{% highlight scala %}
STRING.sha256() {% endhighlight %} Returns the SHA-256 hash of STRING as a string of 64 hexadecimal digits; returns NULL if STRING is NULL.
<tr>
<td>
{% highlight scala %}
STRING.sha384() {% endhighlight %} Returns the SHA-384 hash of STRING as a string of 96 hexadecimal digits; returns NULL if STRING is NULL.
<tr>
<td>
{% highlight scala %}
STRING.sha512() {% endhighlight %} Returns the SHA-512 hash of STRING as a string of 128 hexadecimal digits; returns NULL if STRING is NULL.
<tr>
<td>
{% highlight scala %}
STRING.sha2(INT) {% endhighlight %} 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.
{% top %}
The aggregate functions take an expression across all the rows as the input and return a single aggregated value as the result.
<tr>
<td>
{% highlight text %}
COUNT(*) COUNT(1) {% endhighlight %} Returns the number of input rows.
<tr>
<td>
{% highlight text %}
AVG([ ALL | DISTINCT ] expression) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
SUM([ ALL | DISTINCT ] expression) {% endhighlight %} By default or with keyword ALL, returns the sum of expression across all input rows. Use DISTINCT for one unique instance of each value.
<tr>
<td>
{% highlight text %}
MAX([ ALL | DISTINCT ] expression) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
MIN([ ALL | DISTINCT ] expression) {% endhighlight %} 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. {% highlight text %} STDDEV_POP([ ALL | DISTINCT ] expression) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
STDDEV_SAMP([ ALL | DISTINCT ] expression) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
VAR_POP([ ALL | DISTINCT ] expression) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
VAR_SAMP([ ALL | DISTINCT ] expression) {% endhighlight %} 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.
<tr>
<td>
{% highlight text %}
COLLECT([ ALL | DISTINCT ] expression) {% endhighlight %} 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.
<tr>
<td>
{% highlight java %}
FIELD.avg {% endhighlight %} Returns the average (arithmetic mean) of FIELD across all input rows.
<tr>
<td>
{% highlight java %}
FIELD.sum {% endhighlight %} Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns NULL.
<tr>
<td>
{% highlight java %}
FIELD.sum0 {% endhighlight %} Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns 0.
<tr>
<td>
{% highlight java %}
FIELD.max {% endhighlight %} Returns the maximum value of numeric field FIELD across all input rows.
<tr>
<td>
{% highlight java %}
FIELD.min {% endhighlight %} Returns the minimum value of numeric field FIELD across all input rows.
<tr>
<td>
{% highlight java %}
FIELD.stddevPop {% endhighlight %} Returns the population standard deviation of numeric field FIELD across all input rows.
<tr>
<td>
{% highlight java %}
FIELD.stddevSamp {% endhighlight %} Returns the sample standard deviation of numeric field FIELD across all input rows.
<tr>
<td>
{% highlight java %}
FIELD.varPop {% endhighlight %} Returns the population variance (square of the population standard deviation) of numeric field FIELD across all input rows.
<tr>
<td>
{% highlight java %}
FIELD.varSamp {% endhighlight %} Returns the sample variance (square of the sample standard deviation) of numeric field FIELD across all input rows.
<tr>
<td>
{% highlight java %}
FIELD.collect {% endhighlight %} Returns a multiset of FIELD across all input rows.
<tr>
<td>
{% highlight scala %}
FIELD.avg {% endhighlight %} Returns the average (arithmetic mean) of FIELD across all input rows.
<tr>
<td>
{% highlight scala %}
FIELD.sum {% endhighlight %} Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns NULL.
<tr>
<td>
{% highlight scala %}
FIELD.sum0 {% endhighlight %} Returns the sum of numeric field FIELD across all input rows. If all values are NULL, returns 0.
<tr>
<td>
{% highlight scala %}
FIELD.max {% endhighlight %} Returns the maximum value of numeric field FIELD across all input rows.
<tr>
<td>
{% highlight scala %}
FIELD.min {% endhighlight %} Returns the minimum value of numeric field FIELD across all input rows.
<tr>
<td>
{% highlight scala %}
FIELD.stddevPop {% endhighlight %} Returns the population standard deviation of numeric field FIELD across all input rows.
<tr>
<td>
{% highlight scala %}
FIELD.stddevSamp {% endhighlight %} Returns the sample standard deviation of numeric field FIELD across all input rows.
<tr>
<td>
{% highlight scala %}
FIELD.varPop {% endhighlight %} Returns the population variance (square of the population standard deviation) of numeric field FIELD across all input rows.
<tr>
<td>
{% highlight scala %}
FIELD.varSamp {% endhighlight %} Returns the sample variance (square of the sample standard deviation) of numeric field FIELD across all input rows.
<tr>
<td>
{% highlight scala %}
FIELD.collect {% endhighlight %} Returns a multiset of FIELD across all input rows.
{% top %}
The following table lists specifiers for date format functions.
{% top %}
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) |
{% top %}