SPL-supported SQL functions

Function

Syntax

Description

abs function

abs(x)

Calculates the absolute value of x.

acos function

acos(x)

Calculates the arc cosine of x.

asin function

asin(x)

Calculates the arc sine of x.

atan function

atan(x)

Calculates the arc tangent of x.

atan2 function

atan2(x, y)

Calculates the arc tangent of x divided by y.

cbrt function

cbrt(x)

Calculates the cube root of x.

ceil function

ceil(x）

Rounds x up to the nearest integer.

This function is an alias of the ceiling function.

ceiling function

ceiling(x）

Rounds x up to the nearest integer.

cos function

cos(x)

Calculates the cosine of x.

cosh function

cosh(x)

Calculates the hyperbolic cosine of x.

degrees function

degrees(x)

Converts an angle in radians to its equivalent in degrees.

e function

e()

Returns the value of e, which is the base of the natural logarithm.

floor function

floor(x)

Rounds x down to the nearest integer.

from_base function

from_base(x, y)

Converts x to a base-y number.

ln function

ln(x)

Calculates the natural logarithm of x.

infinity function

infinity()

Returns a value that represents positive infinity.

is_nan function

is_nan(x)

Determines whether x is Not a Number (NaN).

log2 function

log2(x)

Calculates the base-2 logarithm of x.

log10 function

log10(x)

Calculates the base-10 logarithm of x.

mod function

mod(x, y)

Calculates the remainder of x divided by y.

nan function

nan()

Returns a value that is NaN.

pi function

pi()

Returns the value of π to 15 decimal places.

pow function

pow(x, y)

Raises x to the power of y.

This function is an alias of the power function.

power function

power(x, y)

Raises x to the power of y.

radians function

radians(x)

Converts an angle in degrees to its equivalent in radians.

rand function

rand()

Returns a random number.

random function

random()

Returns a random number in the range [0,1).

random(x)

Returns a random number in the range [0,x).

round function

round(x)

Rounds x to the nearest integer.

round(x, n)

Rounds x to the nearest decimal with n decimal places.

sin function

sin(x)

Calculates the sine of x.

sqrt function

sqrt(x)

Calculates the square root of x.

tan function

tan(x)

Calculates the tangent of x.

tanh function

tanh(x)

Calculates the hyperbolic tangent of x.

truncate function

truncate(x)

Removes the fractional part of x.

Function

Syntax

Description

chr function

chr(x)

Converts an ASCII code to characters.

codepoint function

codepoint(x)

Converts characters to an ASCII code.

concat function

concat(x, y...)

Concatenates multiple strings into one string.

from_utf8 function

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 function

length(x)

Returns the length of a string.

lower function

lower(x)

Converts the characters in a string to lowercase letters.

lpad function

lpad(x, length, lpad_string)

Left pads a string to a specified length by using a specified character and returns the result string.

ltrim function

ltrim(x)

Removes spaces from the start of a string.

replace function

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 function

reverse(x)

Returns a string in reverse order.

rpad function

rpad(x, length, rpad_string)

Right pads a string to a specified length by using a specified character and returns the result string.

rtrim function

rtrim(x)

Removes spaces from the end of a string.

split_part function

split_part(x, delimeter, part)

Splits a string by using a specified delimiter and returns the substring at a specified position.

strpos function

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 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 function

to_utf8(x)

Converts a string to a UTF-8 encoded string.

trim function

trim(x)

Removes spaces from the start and end of a string.

upper function

upper(x)

Converts the characters in a string to uppercase letters.

Category

Function

Syntax

Description

Date and time functions

date_format function

date_format(x, format)

Converts a datetime expression that returns a timestamp value to a datetime expression in a specified format.

date_parse function

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 function

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 function

to_unixtime(x)

Converts a datetime expression that returns a timestamp value to a UNIX timestamp.

Date and time extraction functions

day_of_week function

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 function

day_of_year(x)

Returns the day of the year from a datetime expression.

This function is equivalent to the doy function.

dow function

dow(x)

Returns the day of the week from a datetime expression.

This function is equivalent to the day_of_week function.

doy function

doy(x)

Returns the day of the year from a datetime expression.

This function is equivalent to the day_of_year function.

hour function

hour(x)

Returns the hour of the day from a datetime expression. The 24-hour clock is used.

minute function

minute(x)

Returns the minute of the hour from a datetime expression.

month function

month(x)

Returns the month of the year from a datetime expression.

quarter function

quarter(x)

Returns the quarter of the year on which a specified date falls.

second function

second(x)

Returns the second of the minute from a datetime expression.

year function

year(x)

Returns the year of a specified date.

year_of_week function

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 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 function

date_add(unit, N, x)

Adds N to the value of the x field based on the unit that you specify.

date_diff function

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.

Function

Syntax

Description

regexp_extract function

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 function

regexp_like(x, regular expression)

Checks whether a specified string matches a specified regular expression.

regexp_replace function

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.

Function

Syntax

Description

cast function

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.

Function

Syntax

Description

url_encode function

url_encode(x)

Encodes a URL.

url_decode function

url_decode(x)

Decodes a URL.

url_extract_fragment function

url_extract_fragment(x)

Extracts the fragment from a URL.

url_extract_host function

url_extract_host(x)

Extracts the host from a URL.

url_extract_parameter function

url_extract_parameter(x, parameter name)

Extracts the value of a specified parameter in the query string from a URL.

url_extract_path function

url_extract_path(x)

Extracts the path from a URL.

url_extract_port function

url_extract_port(x)

Extracts the port number from a URL.

url_extract_protocol function

url_extract_protocol(x)

Extracts the protocol from a URL.

url_extract_query function

url_extract_query(x)

Extracts the query string from a URL.

Function

Syntax

Description

from_base64 function

from_base64(x)

Decodes a Base64-encoded string into a binary number.

from_hex function

from_hex(x)

Converts a hexadecimal number to a binary number.

md5 function

md5(x)

Computes the MD5 hash value for a binary number.

to_base64 function

to_base64(x)

Encodes a binary number into a Base64 string representation.

to_hex function

to_hex(x)

Converts a binary number to a hexadecimal number.

sha1 function

sha1(x)

Computes the SHA-1 hash value for a binary number.

sha256 function

sha256(x)

Computes the SHA-256 hash value for a binary number.

sha512 function

sha512(x)

Computes the SHA-512 hash value for a binary number.

xxhash64 function

xxhash64(x)

Computes the xxhash64 hash value for a binary number.

Function

Syntax

Description

bit_count function

bit_count(x, bits)

Returns the number of bits 1 in x in binary representation.

bitwise_and function

bitwise_and(x, y)

Returns the result of the bitwise AND operation on x and y in binary representation.

bitwise_not function

bitwise_not(x)

Returns the result of the bitwise NOT operation on x in binary representation.

bitwise_or function

bitwise_or(x, y)

Returns the result of the bitwise OR operation on x and y in binary representation.

bitwise_xor function

bitwise_xor(x, y)

Returns the result of the bitwise XOR operation on x and y in binary representation.

Operator

Syntax

Description

Arithmetic operators

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.

BETWEEN operator

x BETWEEN y AND z

If x is between y and z, true is returned.

LIKE operator

x LIKE pattern [escape 'escape_character']

Checks whether a string matches a specified pattern. The string is case-sensitive.

Operator

Syntax

Description

AND operator

x AND y

If x and y are evaluated to true, true is returned.

OR operator

x OR y

If x or y is evaluated to true, true is returned.

NOT operator

NOT x

If x is evaluated to false, true is returned.

Expression

Syntax

Description

CASE WHEN expression

CASE WHEN condition1 THEN result1

[WHEN condition2 THEN result2]

[ELSE result3]

END

Categorizes data by using conditions.

IF expression

IF(condition, result1)

If condition is evaluated to true, result1 is returned. Otherwise, null is returned.

COALESCE expression

COALESCE(expression1, expression2, expression3...)

Returns the first non-null value in multiple expressions.

TRY expression

TRY(expression)

Evaluates an expression to capture errors. This helps the system continue to query and analyze data.