This topic describes SQL functions supported by SPL statements.
Mathematical functions
Function | Syntax | Description |
abs(x) | Calculates the absolute value of x. | |
acos(x) | Calculates the arc cosine of x. | |
asin(x) | Calculates the arc sine of x. | |
atan(x) | Calculates the arc tangent of x. | |
atan2(x, y) | Calculates the arc tangent of x divided by y. | |
cbrt(x) | Calculates the cube root of x. | |
ceil(x） | Rounds x up to the nearest integer. This function is an alias of the ceiling function. | |
ceiling(x） | Rounds x up to the nearest integer. | |
cos(x) | Calculates the cosine of x. | |
cosh(x) | Calculates the hyperbolic cosine of x. | |
degrees(x) | Converts an angle in radians to its equivalent in degrees. | |
e() | Returns the value of e, which is the base of the natural logarithm. | |
floor(x) | Rounds x down to the nearest integer. | |
from_base(x, y) | Converts x to a base-y number. | |
ln(x) | Calculates the natural logarithm of x. | |
infinity() | Returns a value that represents positive infinity. | |
is_nan(x) | Determines whether x is Not a Number (NaN). | |
log2(x) | Calculates the base-2 logarithm of x. | |
log10(x) | Calculates the base-10 logarithm of x. | |
mod(x, y) | Calculates the remainder of x divided by y. | |
nan() | Returns a value that is NaN. | |
pi() | Returns the value of π to 15 decimal places. | |
pow(x, y) | Raises x to the power of y. This function is an alias of the power function. | |
power(x, y) | Raises x to the power of y. | |
radians(x) | Converts an angle in degrees to its equivalent in radians. | |
rand() | Returns a random number. | |
random() | Returns a random number in the range [0,1). | |
random(x) | Returns a random number in the range [0,x). | |
round(x) | Rounds x to the nearest integer. | |
round(x, n) | Rounds x to the nearest decimal with n decimal places. | |
sin(x) | Calculates the sine of x. | |
sqrt(x) | Calculates the square root of x. | |
tan(x) | Calculates the tangent of x. | |
tanh(x) | Calculates the hyperbolic tangent of x. | |
truncate(x) | Removes the fractional part of x. |
String functions
Function | Syntax | Description |
chr(x) | Converts an ASCII code to characters. | |
codepoint(x) | Converts characters to an ASCII code. | |
concat(x, y...) | Concatenates multiple strings into one string. | |
from_utf8(x) | Decodes a binary string into a UTF-8 encoded string. Invalid UTF-8 characters are replaced with the default replacement character U+FFFD. | |
from_utf8(x, replace_string) | Decodes a binary string into a UTF-8 encoded string. Invalid UTF-8 characters are replaced with a custom string. | |
length(x) | Returns the length of a string. | |
lower(x) | Converts the characters in a string to lowercase letters. | |
lpad(x, length, lpad_string) | Left pads a string to a specified length by using a specified character and returns the result string. | |
ltrim(x) | Removes spaces from the start of a string. | |
replace(x, sub_string ) | Removes the matched characters from a string. | |
replace(x, sub_string, replace_string) | Replaces the matched characters in a string with specified characters. | |
reverse(x) | Returns a string in reverse order. | |
rpad(x, length, rpad_string) | Right pads a string to a specified length by using a specified character and returns the result string. | |
rtrim(x) | Removes spaces from the end of a string. | |
split_part(x, delimeter, part) | Splits a string by using a specified delimiter and returns the substring at a specified position. | |
strpos(x, sub_string) | Returns the position of a specified substring in a string. This function is equivalent to the position(sub_string in x) function. | |
substr(x, start) | Returns the substring at a specified position in a string. | |
substr(x, start, length) | Returns the substring at a specified position in a string. The length of the substring is specified. | |
to_utf8(x) | Converts a string to a UTF-8 encoded string. | |
trim(x) | Removes spaces from the start and end of a string. | |
upper(x) | Converts the characters in a string to uppercase letters. |
Date and time functions
Category | Function | Syntax | Description |
Date and time functions | date_format(x, format) | Converts a datetime expression that returns a timestamp value to a datetime expression in a specified format. | |
date_parse(x, format) | Converts a datetime string to a datetime expression that returns a timestamp value and is in a specified format. | ||
from_unixtime(x) | Converts a UNIX timestamp to a datetime expression that returns a timestamp value and does not contain a time zone. | ||
to_unixtime(x) | Converts a datetime expression that returns a timestamp value to a UNIX timestamp. | ||
Date and time extraction functions | day_of_week(x) | Returns the day of the week from a datetime expression. This function is equivalent to the dow function. | |
day_of_year(x) | Returns the day of the year from a datetime expression. This function is equivalent to the doy function. | ||
dow(x) | Returns the day of the week from a datetime expression. This function is equivalent to the day_of_week function. | ||
doy(x) | Returns the day of the year from a datetime expression. This function is equivalent to the day_of_year function. | ||
hour(x) | Returns the hour of the day from a datetime expression. The 24-hour clock is used. | ||
minute(x) | Returns the minute of the hour from a datetime expression. | ||
month(x) | Returns the month of the year from a datetime expression. | ||
quarter(x) | Returns the quarter of the year on which a specified date falls. | ||
second(x) | Returns the second of the minute from a datetime expression. | ||
year(x) | Returns the year of a specified date. | ||
year_of_week(x) | Returns the year on which a specified date falls in the ISO week date system. This function is equivalent to the yow function. | ||
yow(x) | Returns the year on which a specified date falls in the ISO week date system. This function is equivalent to the year_of_week function. | ||
Time interval functions | date_add(unit, N, x) | Adds N to the value of the x field based on the unit that you specify. | |
date_diff(unit, x, y) | Returns the difference between two time expressions. For example, you can calculate the difference between x and y based on a specified time unit. |
Regular expression functions
Function | Syntax | Description |
regexp_extract(x, regular expression) | Extracts the first substring that matches a specified regular expression from a specified string and returns the substring. | |
regexp_extract(x, regular expression, n) | Extracts the substrings that match a specified regular expression from a specified string and returns the first substring that matches the nth capturing group in the regular expression. | |
regexp_like(x, regular expression) | Checks whether a specified string matches a specified regular expression. | |
regexp_replace(x, regular expression) | Deletes the substrings that match a specified regular expression from a specified string and returns the substrings that remain. | |
regexp_replace(x, regular expression, replace string) | Replaces the substrings that match a specified regular expression in a specified string and returns the result string. |
JSON functions
Function | Syntax | Description |
json_array_contains(x, value) | Checks whether a JSON array contains a specified value. | |
json_array_get(x, index) | Obtains the element that corresponds to an index in a JSON array. | |
json_array_length(x) | Obtains the number of elements in a JSON array. | |
json_extract(x, json_path) | Obtains a set of JSON values from a JSON object or a JSON array. The JSON values can be of the array or object type. | |
json_extract_scalar(x, json_path) | Obtains a set of scalar values from a JSON object or a JSON array. The scalar values can be of the string, integer, or Boolean type. This function is similar to the json_extract function. | |
json_format(x) | Converts JSON data to a string. | |
json_parse(x) | Converts a string to JSON data. | |
json_size(x, json_path) | Obtains the number of elements in a JSON object or a JSON array. |
Data type conversion functions
Function | Syntax | Description |
cast(x as type) | Converts the values of the x field as a data type. If the cast function fails to convert a value, the query that calls this function is terminated. |
URL functions
Function | Syntax | Description |
url_encode(x) | Encodes a URL. | |
url_decode(x) | Decodes a URL. | |
url_extract_fragment(x) | Extracts the fragment from a URL. | |
url_extract_host(x) | Extracts the host from a URL. | |
url_extract_parameter(x, parameter name) | Extracts the value of a specified parameter in the query string from a URL. | |
url_extract_path(x) | Extracts the path from a URL. | |
url_extract_port(x) | Extracts the port number from a URL. | |
url_extract_protocol(x) | Extracts the protocol from a URL. | |
url_extract_query(x) | Extracts the query string from a URL. |
Binary functions
Function | Syntax | Description |
from_base64(x) | Decodes a Base64-encoded string into a binary number. | |
from_hex(x) | Converts a hexadecimal number to a binary number. | |
md5(x) | Computes the MD5 hash value for a binary number. | |
to_base64(x) | Encodes a binary number into a Base64 string representation. | |
to_hex(x) | Converts a binary number to a hexadecimal number. | |
sha1(x) | Computes the SHA-1 hash value for a binary number. | |
sha256(x) | Computes the SHA-256 hash value for a binary number. | |
sha512(x) | Computes the SHA-512 hash value for a binary number. | |
xxhash64(x) | Computes the xxhash64 hash value for a binary number. |
Bitwise functions
Function | Syntax | Description |
bit_count(x, bits) | Returns the number of bits 1 in x in binary representation. | |
bitwise_and(x, y) | Returns the result of the bitwise AND operation on x and y in binary representation. | |
bitwise_not(x) | Returns the result of the bitwise NOT operation on x in binary representation. | |
bitwise_or(x, y) | Returns the result of the bitwise OR operation on x and y in binary representation. | |
bitwise_xor(x, y) | Returns the result of the bitwise XOR operation on x and y in binary representation. |
Comparison operators
Operator | Syntax | Description |
x < y | If x is less than y, true is returned. | |
x > y | If x is greater than y, true is returned. | |
x <= y | If x is less than or equal to y, true is returned. | |
x >= y | If xis greater than or equal to y, true is returned. | |
x = y | If x is equal to y, true is returned. | |
x <> y | If x is not equal to y, true is returned. | |
x != y | If x is not equal to y, true is returned. | |
x BETWEEN y AND z | If x is between y and z, true is returned. | |
x LIKE pattern [escape 'escape_character'] | Checks whether a string matches a specified pattern. The string is case-sensitive. |
Logical operators
Operator | Syntax | Description |
x AND y | If x and y are evaluated to true, true is returned. | |
x OR y | If x or y is evaluated to true, true is returned. | |
NOT x | If x is evaluated to false, true is returned. |
Conditional expressions
Expression | Syntax | Description |
CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2] [ELSE result3] END | Categorizes data by using conditions. | |
IF(condition, result1) | If condition is evaluated to true, result1 is returned. Otherwise, null is returned. | |
COALESCE(expression1, expression2, expression3...) | Returns the first non-null value in multiple expressions. | |
TRY(expression) | Evaluates an expression to capture errors. This helps the system continue to query and analyze data. |