# Functions and Operators

This is supported by JDBC Java DataBase Connectivity. This is an application programming interface (API) for the Java programming language, which defines how a client may access a database. V3

# Aggregate Functions

Function | Explanation |

COUNT(*) :: BIGINT |
Calculates the number of input rows. |

COUNT(any) :: BIGINT |
Calculates the number of input rows in which the input value is not null. |

COUNT(DISTINCT *) :: BIGINT |
Calculates the number of input rows in which the input value is unique. |

COUNT(DISTINCT any) :: BIGINT |
Calculates the number of input rows in which the input value is unique and not null. |

SUM(TINYINT | SMALLINT | INT) :: BIGINT, SUM(BIGINT | DECIMAL) :: DECIMAL, SUM(REAL) :: REAL, SUM(DOUBLE) :: DOUBLE |
Calculates the sum of the non-null input values. |

SUM(DISTINCT TINYINT | SMALLINT | INT) :: BIGINT, SUM(DISTINCT BIGINT | DECIMAL) :: DECIMAL, SUM(DISTINCT REAL) :: REAL, SUM(DISTINCT DOUBLE) :: DOUBLE |
Calculates the sum of the unique, non-null input values. |

AVG(DECIMAL) :: DECIMAL, AVG(DOUBLE) :: DOUBLE |
Calculates the mean of all the non-null input values. |

AVG(DISTINCT DECIMAL) :: DECIMAL, AVG(DISTINCT DOUBLE) :: DOUBLE |
Calculates the mean of all the unique, non-null input values. |

MIN(any) :: same type as the input |
Calculates the minimum of the non-null input values. Applicable also to OBJECT type, if the underlying value is java.lang.Comparable. |

# Comparison Operators

Operator | Explanation |

= |
Equal to |

!= or <> |
Not equal to |

< |
Less than |

> |
Greater than |

<= |
Less than or equal to |

>= |
Greater than or equal to |

# Date and Time Functions

Function | Explanation |

EXTRACT(element, date) |
Extracts element from date. Supported elements: MILLENIUM, CENTURY, DECADE, YEAR, ISOYEAR, QUARTER, MONTH, WEEK, DAY, DOW, ISODOW, DOY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, EPOCH Supported date types: DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE |

TO_TIMESTAMP_TZ(BIGINT) |
Converts BIGINT values as seconds to TIMESTAMP_WITH_TIMEZONE. |

TO_EPOCH_MILLIS(TIMESTAMP_WITH_TIMEZONE) |
Converts TIMESTAMP_WITH_TIMEZONE values to an EPOCH value in milliseconds. |

# Expressions

AND/OR Operators

NOT Operator

IS Operator

IS TRUE |

IS NOT TRUE |

IS FALSE |

IS NOT FALSE |

IS NULL |

IS NOT NULL |

IN Operator

Operator | Explanation |

IN |
Evaluates to TRUE if the value on the left of the operator is in the list of values on the right. |

NOT IN |
Evaluates to TRUE if the value on the left of the operator is not in the list of values on the right. |

# Mathematical Functions

ABS(number) |

CEIL(number) |

DEGREES(double) |

EXP(number) |

FLOOR(number) |

LN(number) |

LOG10(number) |

RADIANS(double) |

RAND |

RAND(number) |

ROUND(number) |

ROUND(number, s integer) |

TRUNCATE(number) |

TRUNCATE(number, s integer) |

# Special Functions

Function | Explanation |

CAST(value AS type) |
Convert the value to the given type |

NULLIF(expression1, expression2) |
Returns NULL if the expressions are equal. The data type of the NULL value returned is the same as the first expression. |

COALESCE( expression1 [, expression2 [, expression3 ] …] ) |
Evaluates each expression at a time, starting from the first. If the value of any expression is not NULL, its value is returned. If all the expressions are NULL, NULL is returned. Arguments to the right of the first non-null argument are not evaluated. |

CASE |
Returns a value when the first condition is met (like an if-then-else statement). |

Function | Explanation |

BETWEEN |
Evaluates to TRUE if the value on the left of the operator is in a given range between the first value on the right and the second value on the right. (a >= b AND a <= c) |

BETWEEN SYMMETRIC |
Evaluates to TRUE if the value on the left of the operator is in a given range between the first value on the right and the second value on the right or the second value on the right and the first value on the right. (a >= b AND a <= c) OR (a >= c AND a <= b). |

NOT BETWEEN |
Evaluates to TRUE if the value on the left of the operator is not in a given range between the first value on the right and the second value on the right. |

NOT BETWEEN SYMMETRIC |
Evaluates to TRUE if the value on the left of the operator is not in a given range between the first value on the right and the second value on the right or the second value on the right and the first value on the right. |

# String Functions

Function | Explanation |

string || string |
Concatenate two strings |

ASCII(string) |
ASCII code of the first character of the argument |

INITCAP(string) |
Convert the first letter of each word to upper case, and the rest REpresentational State Transfer. Application Programming Interface An API, or application programming interface, is a set of rules that define how applications or devices can connect to and communicate with each other. A REST API is an API that conforms to the design principles of the REST, or representational state transfer architectural style. to lower case |

LENGTH(string) |
Length of the string |

LIKE |
Return TRUE if the value string follows the pattern |

LIKE … ESCAPE |
Return TRUE if the value string follows the pattern, escaping a special character in the pattern |

LOWER(string) |
Convert the string to lower case |

LTRIM(string) |
Equivalent to TRIM(LEADING ' ' FROM string) |

RTRIM(string) |
Equivalent to TRIM(TRAILING ' ' FROM string) |

SUBSTRING(string FROM integer) |
Extract substring starting with the given position |

SUBSTRING(string FROM integer FOR integer) |
Extract substring starting with the given position for the given length |

TRIM([LEADING|TRAILING|BOTH] [characters FROM] string) |
Remove characters (a space by default) from the start/end/both ends of the string |

TRIM(characters FROM string) |
Equivalent to TRIM(BOTH characters FROM string) |

TRIM(string) |
Equivalent to TRIM(BOTH ' ' FROM string) |

UPPER(string) |
Convert the string to upper case |