Supported Operators and Functions
Aggregate Functions
AVG
AVG( [ ALL | DISTINCT ] numeric)Returns the average (arithmetic mean) of numeric across all input values. When used, the type of data will be changed in the following way:
| Input type | Result type | Minimum scale |
|---|---|---|
|
|
16 |
|
|
COUNT
COUNT( [ ALL | DISTINCT ] value [, value ]*)Returns the number of input rows for which value is not null (wholly not null if value is composite).
MAX
MAX( [ ALL | DISTINCT ] value)Returns the maximum value across all input values.
MIN
MIN( [ ALL | DISTINCT ] value)Returns the minimum value across all input values.
SUM
SUM( [ ALL | DISTINCT ] numeric)Returns the sum of numeric across all input values.
ANY_VALUE
ANY_VALUE( [ ALL | DISTINCT ] value)Returns one of the values of value across all input values; this is NOT specified in the SQL standard.
EVERY
EVERY(condition)Returns TRUE if all of the values of condition are TRUE.
SOME
SOME(condition)Returns TRUE if one or more of the values of condition is TRUE.
JSON Functions
JSON_TYPE
JSON_TYPE(jsonValue)Returns a string value indicating the type of jsonValue.
FORMAT JSON
Indicates that the value is formatted as JSON.
JSON_VALUE
JSON_VALUE(jsonValue, path [ RETURNING type ] [ { ERROR | NULL | DEFAULT expr } ON EMPTY ] [ { ERROR | NULL | DEFAULT expr } ON ERROR ] )Extract an SQL scalar from a jsonValue using JSON path expression path.
JSON_QUERY
JSON_QUERY(jsonValue, path [ { WITHOUT [ ARRAY ] | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ] } WRAPPER ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON EMPTY ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON ERROR ] )Extract a JSON object or JSON array from jsonValue using the path JSON path expression.
JSON_TYPE
JSON_TYPE(jsonValue)Returns a string value indicating the type of jsonValue.
JSON_EXISTS
JSON_EXISTS(jsonValue, path [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ] )Whether a jsonValue satisfies a search criterion described using JSON path expression path.
JSON_DEPTH
JSON_DEPTH(jsonValue)Returns an integer value indicating the depth of jsonValue.
JSON_KEYS
JSON_KEYS(jsonValue [, path ])Returns a string indicating the keys of a JSON jsonValue.
JSON_PRETTY
JSON_PRETTY(jsonValue)Returns a pretty-printing of jsonValue.
JSON_LENGTH
JSON_LENGTH(jsonValue [, path ])Returns a integer indicating the length of jsonValue.
JSON_REMOVE
JSON_REMOVE(jsonValue, path [, path ])Removes data from jsonValue using a series of path expressions and returns the result.
JSON_STORAGE_SIZE
JSON_STORAGE_SIZE(jsonValue)Returns the number of bytes used to store the binary representation of jsonValue.
JSON_OBJECT
JSON_OBJECT( jsonKeyVal [, jsonKeyVal ]* [ nullBehavior ] )Construct JSON object using a series of key-value pairs.
JSON_ARRAY
JSON_ARRAY( [ jsonVal [, jsonVal ]* ] [ nullBehavior ] )Construct a JSON array using a series of values.
IS JSON VALUE
jsonValue IS JSON [ VALUE ]Whether jsonValue is a JSON value.
IS JSON OBJECT
jsonValue IS JSON OBJECTWhether jsonValue is a JSON object.
IS JSON ARRAY
jsonValue IS JSON ARRAYWhether jsonValue is a JSON array.
IS JSON SCALAR
jsonValue IS JSON SCALARWhether jsonValue is a JSON scalar value.
Regular Expression Functions
POSIX REGEX CASE INSENSITIVE
value 1 POSIX REGEX CASE INSENSITIVE value 2Case-sensitive POSIX regular expression.
POSIX REGEX CASE SENSITIVE
value 1 POSIX REGEX CASE SENSITIVE value 2Case-sensitive POSIX regular expression.
REGEXP_REPLACE
REGEXP_REPLACE(string, regexp, rep [, pos [, occurrence [, matchType]]])Replaces all substrings of string that match regexp with rep at the starting pos in expr (if omitted, the default is 1), occurrence specifies which occurrence of a match to search for (if omitted, the default is 1), matchType specifies how to perform matching
Numeric Functions
MOD
MOD(numeric1, numeric2)Returns the remainder (modulus) of numeric1 divided by numeric2. The result is negative only if numeric1 is negative.
EXP
EXP(numeric)Returns e raised to the power of numeric.
POWER
POWER(numeric1, numeric2)Returns numeric1 raised to the power of numeric2.
LN
LN(numeric)Returns the natural logarithm (base e) of numeric.
LOG10
LOG10(numeric)Returns the base 10 logarithm of numeric.
ABS
ABS(numeric)Returns the absolute value of numeric.
RAND
RAND([seed])Generates a random double between 0 and 1 inclusive, optionally initializing the random number generator with seed.
RAND_INTEGER
RAND_INTEGER([seed, ] numeric)Generates a random integer between 0 and numeric - 1 inclusive, optionally initializing the random number generator with seed.
ACOS
ACOS(numeric)Returns the arc cosine of numeric.
ASIN
ASIN(numeric)Returns the arc sine of numeric.
ATAN
ATAN(numeric)Returns the arc tangent of numeric.
ATAN2
ATAN2(numeric, numeric)Returns the arc tangent of the numeric coordinates.
SQRT
SQRT(numeric)Returns the square root of numeric.
CBRT
CBRT(numeric)Returns the cube root of numeric.
COS
COS(numeric)Returns the cosine of numeric.
COSH
COSH(numeric)Returns the hyperbolic cosine of numeric.
COT
COT(numeric)Returns the cotangent of numeric.
DEGREES
DEGREES(numeric)Converts numeric from radians to degrees.
RADIANS
RADIANS(numeric)Converts numeric from degrees to radians.
ROUND
ROUND(numeric1 [, integer2])Rounds numeric1 to optionally integer2 (if not specified 0) places right to the decimal point.
SIGN
SIGN(numeric)Returns the signum of numeric.
SIN
SIN(numeric)Returns the sine of numeric.
SINH
SINH(numeric)Returns the hyperbolic sine of numeric.
TAN
TAN(numeric)Returns the tangent of numeric.
TANH
TANH(numeric)Returns the hyperbolic tangent of numeric.
TRUNCATE
TRUNCATE(numeric1 [, integer2])Truncates numeric1 to optionally integer2 (if not specified 0) places right to the decimal point.
PI
PI()Returns a value that is closer than any other value to Pi.
String Functions
UPPER
UPPER(string)Returns a character string converted to upper case.
LOWER
LOWER(string)Returns a character string converted to lower case.
INITCAP
INITCAP(string)Returns string with the first letter of each word converter to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.
TO_BASE64
TO_BASE64(string)Converts the string to base-64 encoded form and returns a encoded string
FROM_BASE64
FROM_BASE64(string)Returns the decoded result of a base-64 string as a string.
MD5
MD5(string)Calculates an MD5 128-bit checksum of string and returns it as a hex string.
SHA1
SHA1(string)Calculates a SHA-1 hash value of string and returns it as a hex string.
SUBSTRING
SUBSTRING(string FROM integer)Returns a substring of a character string starting at a given point.
SUBSTRING(string FROM integer FOR integer)Returns a substring of a character string starting at a given point with a given length.
SUBSTRING(binary FROM integer)Returns a substring of binary starting at a given point.
SUBSTRING(binary FROM integer FOR integer)Returns a substring of binary starting at a given point with a given length.
LEFT
LEFT(string, length)Returns the leftmost length characters from the string.
RIGHT
RIGHT(string, length)Returns the rightmost length characters from the string.
REPLACE
REPLACE(char, search_string [, replace_string])Replaces search_string with replace_string.
TRANSLATE
TRANSLATE(expr, fromString, toString)Returns expr with all occurrences of each character in fromString replaced by its corresponding character in toString. Characters in expr that are not in fromString are not replaced.
CHR
CHR(integer)Returns the character whose UTF-8 code is integer.
CHAR_LENGTH
CHAR_LENGTH(string)Returns the number of characters in a character string.
CHARACTER_LENGTH
CHARACTER_LENGTH(string)Returns the number of characters in a character string.
||
string || stringConcatenates two character strings.
CONCAT
CONCAT(string, string)Concatenates two strings, returns null only when both string arguments are null, otherwise treats null as empty string.
CONCAT(string [, string ]*)Concatenates one or more strings, returns null if any of the arguments is null.
CONCAT(string [, string ]*)Concatenates one or more strings, null is treated as empty string.
OVERLAY
OVERLAY(string1 PLACING string2 FROM integer [ FOR integer2 ])Replaces a substring of string1 with string2.
OVERLAY(binary1 PLACING binary2 FROM integer [ FOR integer2 ])Replaces a substring of binary1 with binary2.
POSITION
POSITION(substring IN string)Returns the position of the first occurrence of substring in string.
POSITION(substring IN string FROM integer)Returns the position of the first occurrence of substring in string starting at a given point (not standard SQL).
POSITION(binary1 IN binary2)Returns the position of the first occurrence of binary1 in binary2.
POSITION(binary1 IN binary2 FROM integer)Returns the position of the first occurrence of binary1 in binary2 starting at a given point (not standard SQL).
ASCII
ASCII(string)Returns the ASCII code of the first character of string; if the first character is a non-ASCII character, returns its Unicode code point; returns 0 if string is empty.
REPEAT
REPEAT(string, integer)Returns a string consisting of string repeated of integer times; returns an empty string if integer is less than 1.
SPACE
SPACE(integer)Returns a string of integer spaces; returns an empty string if integer is less than 1.
STRCMP
STRCMP(string, string)Returns 0 if both of the strings are same and returns -1 when the first argument is smaller than the second and 1 when the second one is smaller than the first one.
SOUNDEX
SOUNDEX(string)-
Returns the phonetic representation of string; throws if string is encoded with multi-byte encoding such as UTF-8; or
-
Returns the phonetic representation of string; return original string if string is encoded with multi-byte encoding such as UTF-8
DIFFERENCE
DIFFERENCE(string, string)Returns a measure of the similarity of two strings, namely the number of character positions that their SOUNDEX values have in common: 4 if the SOUNDEX values are same and 0 if the SOUNDEX values are totally different.
REVERSE
REVERSE(string)Returns string with the order of the characters reversed.
TRIM
TRIM( { BOTH | LEADING | TRAILING } string1 FROM string2)Removes the longest string containing only the characters in string1 from the start/end/both ends of string1.
LTRIM
LTRIM(string)Returns string with all blanks removed from the start.
RTRIM
RTRIM(string)Returns string with all blanks removed from the end.
SUBSTR
SUBSTR(string, position [, substringLength ])Returns a portion of string, beginning at character position, substringLength characters long. SUBSTR calculates lengths using characters as defined by the input character set.
LENGTH
LENGTH(string)Equivalent to CHAR_LENGTH(string).
OCTET_LENGTH
OCTET_LENGTH(binary)Returns the number of bytes in binary.
LIKE
string1 LIKE string2 [ ESCAPE string3 ]Whether string1 matches pattern string2.
SIMILAR TO
string1 SIMILAR TO string2 [ ESCAPE string3 ]Whether string1 matches regular expression string2.
Date/Time Functions
EXTRACT
EXTRACT(timeUnit FROM datetime)Extracts and returns the value of a specified datetime field from a datetime value expression.
FLOOR
FLOOR(datetime TO timeUnit)Rounds datetime down to timeUnit.
CEIL
CEIL(datetime TO timeUnit)Rounds datetime up to timeUnit.
TIMESTAMPDIFF
TIMESTAMPDIFF(timeUnit, datetime, datetime2)Returns the (signed) number of timeUnit intervals between datetime and datetime2. Equivalent to (datetime2 - datetime) timeUnit.
LAST_DAY
LAST_DAY(date)Returns the date of the last day of the month in a value of datatype DATE; For example, it returns DATE’2020-02-29’ for both DATE’2020-02-10’ and TIMESTAMP’2020-02-10 10:10:10’.
DAYNAME
DAYNAME(datetime)Returns the name of the day of the week based on the datetime value.
MONTHNAME
MONTHNAME(date)Returns the name, in the connection’s locale, of the month in datetime; for example, it returns ‘二月’ for both DATE ‘2020-02-10’ and TIMESTAMP ‘2020-02-10 10:10:10’.
DAYOFMONTH
DAYOFMONTH(date)Equivalent to EXTRACT(DAY FROM date). Returns an integer between 1 and 31.
DAYOFWEEK
DAYOFWEEK(date)Equivalent to EXTRACT(DOW FROM date). Returns an integer between 1 and 7.
DAYOFYEAR
DAYOFYEAR(date)Equivalent to EXTRACT(DOY FROM date). Returns an integer between 1 and 366.
YEAR
YEAR(date)Equivalent to EXTRACT(YEAR FROM date). Returns an integer.
QUARTER
QUARTER(date)Equivalent to EXTRACT(QUARTER FROM date). Returns an integer between 1 and 4.
MONTH
MONTH(date)Equivalent to EXTRACT(MONTH FROM date). Returns an integer between 1 and 12.
WEEK
WEEK(date)Equivalent to EXTRACT(WEEK FROM date). Returns an integer between 1 and 53.
HOUR
HOUR(date)Equivalent to EXTRACT(HOUR FROM date). Returns an integer between 0 and 23.
MINUTE
MINUTE(date)Equivalent to EXTRACT(MINUTE FROM date). Returns an integer between 0 and 59.
SECOND
SECOND(date)Equivalent to EXTRACT(SECOND FROM date). Returns an integer between 0 and 59.
TIMESTAMP_SECONDS
TIMESTAMP_SECONDS(integer)Returns the TIMESTAMP that is integer seconds after 1970-01-01 00:00:00.
TIMESTAMP_MILLIS
TIMESTAMP_MILLIS(integer)Returns the TIMESTAMP that is integer milliseconds after 1970-01-01 00:00:00.
TIMESTAMP_MICROS
TIMESTAMP_MICROS(integer)Returns the TIMESTAMP that is integer microseconds after 1970-01-01 00:00:00.
UNIX_SECONDS
UNIX_SECONDS(timestamp)Returns the number of seconds since 1970-01-01 00:00:00.
UNIX_MILLIS
UNIX_MILLIS(timestamp)Returns the number of milliseconds since 1970-01-01 00:00:00.
UNIX_MICROS
UNIX_MICROS(timestamp)Returns the number of microseconds since 1970-01-01 00:00:00.
UNIX_DATE
UNIX_DATE(date)Returns the number of days since 1970-01-01
DATE_FROM_UNIX_DATE
DATE_FROM_UNIX_DATE(integer)Returns the DATE that is integer days after 1970-01-01.
DATE
DATE(timestamp)Extracts the DATE from a timestamp.
DATE(timestampLtz)Extracts the DATE from timestampLtz (an instant; BigQuery’s TIMESTAMP type), assuming UTC.
DATE(timestampLtz, timeZone)Extracts the DATE from timestampLtz (an instant; BigQuery’s TIMESTAMP type) in timeZone.
DATE(string)Equivalent to CAST(string AS DATE).
DATE(year, month, day)Returns a DATE value for year, month, and day (all of type INTEGER).
CURRENT_TIME
CURRENT_TIMEReturns the current time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE.
CURRENT_TIMESTAMP
CURRENT_TIMESTAMPReturns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE.
CURRENT_DATE
CURRENT_DATEReturns the current date in the session time zone, in a value of datatype DATE.
LOCALTIME
LOCALTIMEReturns the current date and time in the session time zone in a value of datatype TIME.
LOCALTIME(precision)Returns the current date and time in the session time zone in a value of datatype TIME, with precision digits of precision.
LOCALTIMESTAMP
LOCALTIMESTAMPReturns the current date and time in the session time zone in a value of datatype TIMESTAMP.
LOCALTIMESTAMP(precision)Returns the current date and time in the session time zone in a value of datatype TIMESTAMP, with precision digits of precision.
Other Functions
CAST
CAST(value AS type)Converts a value to a given type. Casts between integer types truncate towards 0.
COALESCE
COALESCE(value, value [, value ]*)Provides a value if the first value is null. For example, COALESCE(NULL, 5) returns 5.
GREATEST
GREATEST(expr [, expr ]*)Returns the greatest of the expressions.
NULLIF
NULLIF(value, value)Returns NULL if the values are the same. For example, NULLIF(5, 5) returns NULL; NULLIF(5, 0) returns 5.
NVL
NVL(value1, value2)Returns value1 if value1 is not null, otherwise value2.
CASE
CASE value
WHEN value1 [, value11 ]* THEN result1
[ WHEN valueN [, valueN1 ]* THEN resultN ]*
[ ELSE resultZ ]
ENDSimple case.
CASE
WHEN condition1 THEN result1
[ WHEN conditionN THEN resultN ]*
[ ELSE resultZ ]
ENDSearched case.
DECODE
DECODE(value, value1, result1 [, valueN, resultN ]* [, default ])Compares value to each valueN value one by one; if value is equal to a valueN, returns the corresponding resultN, else returns default, or NULL if default is not specified.
LEAST
LEAST(expr [, expr ]* )Returns the least of the expressions.
COMPRESS
COMPRESS(string)Compresses a string using zlib compression and returns the result as a binary string.
TYPEOF
TYPEOF valueReturns the type of the specified value.
RAND_UUID
RAND_UUIDGenerates a random UUID.
SYSTEM_RANGE
SYSTEM_RANGE(start, end[, increment]Returns a range from the table, with an optional increment.
Sequence Functions
NEXTVAL
NEXTVAL('seq_name')Advances the sequence and returns the new value.
SETVAL
SETVAL('seq_name', bigint)Sets the current sequence value to the specified value.
CURRVAL
CURRVAL('seq_name')Returns the current sequence value.
© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.