blob: 8e368ac13e05d146f2ee07e2a642f57568970316 [file] [log] [blame]
 AsterixDB – Builtin Functions

Builtin Functions

The system provides various classes of functions to support operations on numeric, string, spatial, and temporal data. This document explains how to use these functions.

Numeric Functions

abs

• Syntax:

abs(numeric_value)

• Computes the absolute value of the argument.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• The absolute value of the argument with the same type as the input argument,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error.
• Example:

{ "v1": abs(2013), "v2": abs(-4036), "v3": abs(0), "v4": abs(float("-2013.5")), "v5": abs(double("-2013.593823748327284")) };

• The expected result is:

{ "v1": 2013, "v2": 4036, "v3": 0, "v4": 2013.5, "v5": 2013.5938237483274 }

acos

• Syntax:

acos(numeric_value)

• Computes the arc cosine value of the argument.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• the double arc cosine in radians for the argument, if the argument is in the range of -1 (inclusive) to 1 (inclusive),
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error,
• NaN for other legitimate numeric values.
• Example:

{ "v1": acos(1), "v2": acos(2), "v3": acos(0), "v4": acos(float("0.5")), "v5": acos(double("-0.5")) };

• The expected result is:

{ "v1": 0.0, "v2": NaN, "v3": 1.5707963267948966, "v4": 1.0471975511965979, "v5": 2.0943951023931957 }

asin

• Syntax:

asin(numeric_value)

• Computes the arc sine value of the argument.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• the double arc sin in radians for the argument, if the argument is in the range of -1 (inclusive) to 1 (inclusive),
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error,
• NaN for other legitimate numeric values.
• Example:

{ "v1": asin(1), "v2": asin(2), "v3": asin(0), "v4": asin(float("0.5")), "v5": asin(double("-0.5")) };

• The expected result is:

{ "v1": 1.5707963267948966, "v2": NaN, "v3": 0.0, "v4": 0.5235987755982989, "v5": -0.5235987755982989 }

atan

• Syntax:

atan(numeric_value)

• Computes the arc tangent value of the argument.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• the double arc tangent in radians for the argument,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error.
• Example:

{ "v1": atan(1), "v2": atan(2), "v3": atan(0), "v4": atan(float("0.5")), "v5": atan(double("1000")) };

• The expected result is:

{ "v1": 0.7853981633974483, "v2": 1.1071487177940904, "v3": 0.0, "v4": 0.4636476090008061, "v5": 1.5697963271282298 }

atan2

• Syntax:

atan2(numeric_value1, numeric_value2)

• Computes the arc tangent value of numeric_value2/numeric_value1.

• Arguments:
• numeric_value1: a tinyint/smallint/integer/bigint/float/double value,
• numeric_value2: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• the double arc tangent in radians for numeric_value1 and numeric_value2,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-numeric input value will cause a type error.
• Example:

{ "v1": atan2(1, 2), "v2": atan2(0, 4), "v3": atan2(float("0.5"), double("-0.5")) };

• The expected result is:

{ "v1": 0.4636476090008061, "v2": 0.0, "v3": 2.356194490192345 }

ceil

• Syntax:

ceil(numeric_value)

• Computes the smallest (closest to negative infinity) number with no fractional part that is not less than the value of the argument. If the argument is already equal to mathematical integer, then the result is the same as the argument.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• The ceiling value for the given number in the same type as the input argument,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error.
• Example:

{
"v1": ceil(2013),
"v2": ceil(-4036),
"v3": ceil(0.3),
"v4": ceil(float("-2013.2")),
"v5": ceil(double("-2013.893823748327284"))
};

• The expected result is:

{ "v1": 2013, "v2": -4036, "v3": 1.0, "v4": -2013.0, "v5": -2013.0 }

cos

• Syntax:

cos(numeric_value)

• Computes the cosine value of the argument.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• the double cosine value for the argument,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error.
• Example:

{ "v1": cos(1), "v2": cos(2), "v3": cos(0), "v4": cos(float("0.5")), "v5": cos(double("1000")) };

• The expected result is:

{ "v1": 0.5403023058681398, "v2": -0.4161468365471424, "v3": 1.0, "v4": 0.8775825618903728, "v5": 0.562379076290703 }

exp

• Syntax:

exp(numeric_value)

• Computes enumeric_value.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• enumeric_value,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error.
• Example:

{ "v1": exp(1), "v2": exp(2), "v3": exp(0), "v4": exp(float("0.5")), "v5": exp(double("1000")) };

• The expected result is:

{ "v1": 2.718281828459045, "v2": 7.38905609893065, "v3": 1.0, "v4": 1.6487212707001282, "v5": Infinity }

floor

• Syntax:

floor(numeric_value)

• Computes the largest (closest to positive infinity) number with no fractional part that is not greater than the value. If the argument is already equal to mathematical integer, then the result is the same as the argument.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• The floor value for the given number in the same type as the input argument,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error.
• Example:

{
"v1": floor(2013),
"v2": floor(-4036),
"v3": floor(0.8),
"v4": floor(float("-2013.2")),
"v5": floor(double("-2013.893823748327284"))
};

• The expected result is:

{ "v1": 2013, "v2": -4036, "v3": 0.0, "v4": -2014.0, "v5": -2014.0 }

ln

• Syntax:

ln(numeric_value)

• Computes logenumeric_value.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• logenumeric_value,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error.
• Example:

{ "v1": ln(1), "v2": ln(2), "v3": ln(0), "v4": ln(float("0.5")), "v5": ln(double("1000")) };

• The expected result is:

{ "v1": 0.0, "v2": 0.6931471805599453, "v3": -Infinity, "v4": -0.6931471805599453, "v5": 6.907755278982137 }

log

• Syntax:

log(numeric_value)

• Computes log10numeric_value.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• log10numeric_value,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error.
• Example:

{ "v1": log(1), "v2": log(2), "v3": log(0), "v4": log(float("0.5")), "v5": log(double("1000")) };

• The expected result is:

{ "v1": 0.0, "v2": 0.3010299956639812, "v3": -Infinity, "v4": -0.3010299956639812, "v5": 3.0 }

power

• Syntax:

power(numeric_value1, numeric_value2)

• Computes numeric_value1numeric_value2.

• Arguments:
• numeric_value1: a tinyint/smallint/integer/bigint/float/double value,
• numeric_value2: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• numeric_value1numeric_value2,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-numeric input value will cause a type error.
• Example:

{ "v1": power(1, 2), "v3": power(0, 4), "v4": power(float("0.5"), double("-0.5")) };

• The expected result is:

{ "v1": 1, "v3": 0, "v4": 1.4142135623730951 }

round

• Syntax:

round(numeric_value)

• Computes the number with no fractional part that is closest (and also closest to positive infinity) to the argument.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• The rounded value for the given number in the same type as the input argument,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error.
• Example:

{
"v1": round(2013),
"v2": round(-4036),
"v3": round(0.8),
"v4": round(float("-2013.256")),
"v5": round(double("-2013.893823748327284"))
};

• The expected result is:

{ "v1": 2013, "v2": -4036, "v3": 1.0, "v4": -2013.0, "v5": -2014.0 }

round_half_to_even

• Syntax:

round_half_to_even(numeric_value, [precision])

• Computes the closest numeric value to numeric_value that is a multiple of ten to the power of minus precision. precision is optional and by default value 0 is used.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• precision: an optional tinyint/smallint/integer/bigint field representing the number of digits in the fraction of the the result
• Return Value:
• The rounded value for the given number in the same type as the input argument,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• a type error will be raised if:
• the first argument is any other non-numeric value,
• or, the second argument is any other non-tinyint, non-smallint, non-integer, or non-bigint value.
• Example:

{
"v1": round_half_to_even(2013),
"v2": round_half_to_even(-4036),
"v3": round_half_to_even(0.8),
"v4": round_half_to_even(float("-2013.256")),
"v5": round_half_to_even(double("-2013.893823748327284")),
"v6": round_half_to_even(double("-2013.893823748327284"), 2),
"v7": round_half_to_even(2013, 4),
"v8": round_half_to_even(float("-2013.256"), 5)
};

• The expected result is:

{ "v1": 2013, "v2": -4036, "v3": 1.0, "v4": -2013.0, "v5": -2014.0, "v6": -2013.89, "v7": 2013, "v8": -2013.256 }

sign

• Syntax:

sign(numeric_value)

• Computes the sign of the argument.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• the sign (a tinyint) of the argument, -1 for negative values, 0 for 0, and 1 for positive values,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error.
• Example:

{ "v1": sign(1), "v2": sign(2), "v3": sign(0), "v4": sign(float("0.5")), "v5": sign(double("-1000")) };

• The expected result is:

{ "v1": 1, "v2": 1, "v3": 0, "v4": 1, "v5": -1 }

sin

• Syntax:

sin(numeric_value)

• Computes the sine value of the argument.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• the double sine value for the argument,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error.
• Example:

{ "v1": sin(1), "v2": sin(2), "v3": sin(0), "v4": sin(float("0.5")), "v5": sin(double("1000")) };

• The expected result is:

{ "v1": 0.8414709848078965, "v2": 0.9092974268256817, "v3": 0.0, "v4": 0.479425538604203, "v5": 0.8268795405320025 }

sqrt

• Syntax:

sqrt(numeric_value)

• Computes the square root of the argument.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• the double square root value for the argument,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error.
• Example:

{ "v1": sqrt(1), "v2": sqrt(2), "v3": sqrt(0), "v4": sqrt(float("0.5")), "v5": sqrt(double("1000")) };

• The expected result is:

{ "v1": 1.0, "v2": 1.4142135623730951, "v3": 0.0, "v4": 0.7071067811865476, "v5": 31.622776601683793 }

tan

• Syntax:

tan(numeric_value)

• Computes the tangent value of the argument.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value.
• Return Value:
• the double tangent value for the argument,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-numeric input value will cause a type error.
• Example:

{ "v1": tan(1), "v2": tan(2), "v3": tan(0), "v4": tan(float("0.5")), "v5": tan(double("1000")) };

• The expected result is:

{ "v1": 1.5574077246549023, "v2": -2.185039863261519, "v3": 0.0, "v4": 0.5463024898437905, "v5": 1.4703241557027185 }

trunc

• Syntax:

trunc(numeric_value, number_digits)

• Truncates the number to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.

• Arguments:
• numeric_value: a tinyint/smallint/integer/bigint/float/double value,
• number_digits: a tinyint/smallint/integer/bigint value.
• Return Value:
• the double tangent value for the argument,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is missing,
• a type error will be raised if:
• the first argument is any other non-numeric value,
• the second argument is any other non-tinyint, non-smallint, non-integer, and non-bigint value.
• Example:

{ "v1": trunc(1, 1), "v2": trunc(2, -2), "v3": trunc(0.122, 2), "v4": trunc(float("11.52"), -1), "v5": trunc(double("1000.5252"), 3) };

• The expected result is:

{ "v1": 1, "v2": 2, "v3": 0.12, "v4": 10.0, "v5": 1000.525 }

String Functions

concat

• Syntax:

concat(string1, string2, ...)

• Returns a concatenated string from arguments.

• Arguments:
• string1: a string value,
• string2: a string value,
• ….
• Return Value:
• a concatenated string from arguments,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error.
• Example:

concat("test ", "driven ", "development");

• The expected result is:

"test driven development"

contains

• Syntax:

contains(string, substring_to_contain)

• Checks whether the string string contains the string substring_to_contain

• Arguments:
• string : a string that might contain the given substring,
• substring_to_contain : a target string that might be contained.
• Return Value:
• a boolean value, true if string contains substring_to_contain,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error,
• false otherwise.
• Note: an n_gram index can be utilized for this function.

• Example:

{ "v1": contains("I like iphone", "phone"), "v2": contains("one", "phone") };

• The expected result is:

{ "v1": true, "v2": false }

ends_with

• Syntax:

ends_with(string, substring_to_end_with)

• Checks whether the string string ends with the string substring_to_end_with.

• Arguments:
• string : a string that might end with the given string,
• substring_to_end_with : a string that might be contained as the ending substring.
• Return Value:
• a boolean value, true if string contains substring_to_contain,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error,
• false otherwise.
• Example:

{
"v1": ends_with(" love sprint its shortcut_menu is awesome:)", ":)"),
"v2": ends_with(" awsome:)", ":-)")
};

• The expected result is:

{ "v1": true, "v2": false }

initcap (or title)

• Syntax:

initcap(string)

• Converts a given string string so that the first letter of each word is uppercase and every other letter is lowercase. The function has an alias called “title”.

• Arguments:
• string : a string to be converted.
• Return Value:
• a string as the title form of the given string,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-string input value will cause a type error.
• Example:

{ "v1": initcap("ASTERIXDB is here!"), "v2": title("ASTERIXDB is here!") };

• The expected result is:

{ "v1": "Asterixdb Is Here!", "v2": "Asterixdb Is Here!" }

length

• Syntax:

length(string)

• Returns the length of the string string.

• Arguments:
• string : a string or null that represents the string to be checked.
• Return Value:
• an bigint that represents the length of string,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-string input value will cause a type error.
• Example:

length("test string");

• The expected result is:

11

lower

• Syntax:

lower(string)

• Converts a given string string to its lowercase form.

• Arguments:
• string : a string to be converted.
• Return Value:
• a string as the lowercase form of the given string,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-string input value will cause a type error.
• Example:

lower("ASTERIXDB");

• The expected result is:

"asterixdb"

ltrim

• Syntax:

ltrim(string[, chars]);

• Returns a new string with all leading characters that appear in chars removed. By default, white space is the character to trim.

• Arguments:
• string : a string to be trimmed,
• chars : a string that contains characters that are used to trim.
• Return Value:
• a trimmed, new string,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error.
• Example:

ltrim("me like iphone", "eml");

• The expected result is:

" like iphone"

position

• Syntax:

position(string, string_pattern)

• Returns the first position of string_pattern within string.

• Arguments:
• string : a string that might contain the pattern,
• string_pattern : a pattern string to be matched.
• Return Value:
• the first position that string_pattern appears within string, or -1 if it does not appear,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error.
• Example:

{
"v1": position("ppphonepp", "phone"),
"v2": position("hone", "phone")
};

• The expected result is:

{ "v1": 2, "v2": -1 }

regexp_contains

• Syntax:

regexp_contains(string, string_pattern[, string_flags])

• Checks whether the strings string contains the regular expression pattern string_pattern (a Java regular expression pattern).

• Arguments:
• string : a string that might contain the pattern,
• string_pattern : a pattern string to be matched,
• string_flag : (Optional) a string with flags to be used during regular expression matching.
• The following modes are enabled with these flags: dotall (s), multiline (m), case_insensitive (i), and comments and whitespace (x).
• Return Value:
• a boolean, returns true if string contains the pattern string_pattern,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error,
• false otherwise.
• Example:

{
"v1": regexp_contains("pphonepp", "p*hone"),
"v2": regexp_contains("hone", "p+hone")
}

• The expected result is:

{ "v1": true, "v2": false }

regexp_like

• Syntax:

regexp_like(string, string_pattern[, string_flags])

• Checks whether the string string exactly matches the regular expression pattern string_pattern (a Java regular expression pattern).

• Arguments:
• string : a string that might contain the pattern,
• string_pattern : a pattern string that might be contained,
• string_flag : (Optional) a string with flags to be used during regular expression matching.
• The following modes are enabled with these flags: dotall (s), multiline (m), case_insensitive (i), and comments and whitespace (x).
• Return Value:
• a boolean value, true if string contains the pattern string_pattern,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error,
• false otherwise.
• Example:

{
"v1": regexp_like(" can't stand at&t the network is horrible:(", ".*at&t.*"),
"v2": regexp_like("at&t", ".*att.*")
};

• The expected result is:

{ "v1": true, "v2": false }

regexp_position

• Syntax:

regexp_position(string, string_pattern[, string_flags])

• Returns first position of the regular expression string_pattern (a Java regular expression pattern) within string.

• Arguments:
• string : a string that might contain the pattern,
• string_pattern : a pattern string to be matched,
• string_flag : (Optional) a string with flags to be used during regular expression matching.
• The following modes are enabled with these flags: dotall (s), multiline (m), case_insensitive (i), and comments and whitespace (x).
• Return Value:
• the first position that the regular expression string_pattern appears in string, or -1 if it does not appear.
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error.
• Example:

{
"v1": regexp_position("pphonepp", "p*hone"),
"v2": regexp_position("hone", "p+hone")
};

• The expected result is:

{ "v1": 0, "v2": -1 }

regexp_replace

• Syntax:

regexp_replace(string, string_pattern, string_replacement[, string_flags])

• Checks whether the string string matches the given regular expression pattern string_pattern (a Java regular expression pattern), and replace the matched pattern string_pattern with the new pattern string_replacement.

• Arguments:
• string : a string that might contain the pattern,
• string_pattern : a pattern string to be matched,
• string_replacement : a pattern string to be used as the replacement,
• string_flag : (Optional) a string with flags to be used during replace.
• The following modes are enabled with these flags: dotall (s), multiline (m), case_insensitive (i), and comments and whitespace (x).
• Return Value:
• Returns a string that is obtained after the replacements,
• missing if any argument is a missing value,
• any other non-string input value will cause a type error,
• null if any argument is a null value but no argument is a missing value.
• Example:

regexp_replace(" like iphone the voicemail_service is awesome", " like iphone", "like android")

• The expected result is:

"like android the voicemail_service is awesome"

repeat

• Syntax:

repeat(string, n)

• Returns a string formed by repeating the input string n times.

• Arguments:
• string : a string to be repeated,
• offset : an tinyint/smallint/integer/bigint value as the starting offset of the substring in string.
• Return Value:
• a string that repeats the input string n times,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• a type error will be raised if:
• the first argument is any other non-string value,
• or, the second argument is not a tinyint, smallint, integer, or bigint.
• Example:

repeat("test", 3);

• The expected result is:

"testtesttest"

rtrim

• Syntax:

rtrim(string[, chars]);

• Returns a new string with all trailing characters that appear in chars removed. By default, white space is the character to trim.

• Arguments:
• string : a string to be trimmed,
• chars : a string that contains characters that are used to trim.
• Return Value:
• a trimmed, new string,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error.
• Example:

{
"v1": rtrim("i like iphone", "iphone"),
"v2": rtrim("i like iphone", "oneiph")
};

• The expected result is:

{ "v1": "i like ", "v2": "i like " }

split

• Syntax:

split(string, sep)

• Splits the input string into an array of substrings separated by the string sep.

• Arguments:
• string : a string to be split.
• Return Value:
• an array of substrings by splitting the input string by sep,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-string input value will cause a type error.
• Example:

split("test driven development", " ");

• The expected result is:

[ "test", "driven", "development" ]

starts_with

• Syntax:

starts_with(string, substring_to_start_with)

• Checks whether the string string starts with the string substring_to_start_with.

• Arguments:
• string : a string that might start with the given string.
• substring_to_start_with : a string that might be contained as the starting substring.
• Return Value:
• a boolean, returns true if string starts with the string substring_to_start_with,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error,
• false otherwise.
• Example:

{
"v1" : starts_with(" like the plan, amazing", " like"),
"v2" : starts_with("I like the plan, amazing", " like")
};

• The expected result is:

{ "v1": true, "v2": false }

string_concat

• Syntax:

string_concat(array)

• Concatenates an array of strings array into a single string.

• Arguments:
• array : an array or multiset of strings (could be null or missing) to be concatenated.
• Return Value:
• the concatenated string value,
• missing if the argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• missing if any element in the input array is missing,
• null if any element in the input array is null but no element in the input array is missing,
• any other non-array input value or non-integer element in the input array will cause a type error.
• Example:

string_concat(["ASTERIX", " ", "ROCKS!"]);

• The expected result is:

"ASTERIX ROCKS!"

string_join

• Syntax:

string_join(array, string)

• Joins an array or multiset of strings array with the given separator string into a single string.

• Arguments:
• array : an array or multiset of strings (could be null) to be joined.
• string : a string to serve as the separator.
• Return Value:
• the joined string,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• missing if the first argument array contains a missing,
• null if the first argument array contains a null but does not contain a missing,
• a type error will be raised if:
• the first argument is any other non-array value, or contains any other non-string value,
• or, the second argument is any other non-string value.
• Example:

string_join(["ASTERIX", "ROCKS~"], "!! ");

• The expected result is:

"ASTERIX!! ROCKS~"

string_to_codepoint

• Syntax:

string_to_codepoint(string)

• Converts the string string to its code_based representation.

• Arguments:
• string : a string that will be converted.
• Return Value:
• an array of the code points for the string string,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-string input value will cause a type error.
• Example:

string_to_codepoint("Hello ASTERIX!");

• The expected result is:

[ 72, 101, 108, 108, 111, 32, 65, 83, 84, 69, 82, 73, 88, 33 ]

codepoint_to_string

• Syntax:

codepoint_to_string(array)

• Converts the ordered code_based representation array to the corresponding string.

• Arguments:
• array : an array of integer code_points.
• Return Value:
• a string representation of array.
• missing if the argument is a missing value,
• null if the argument is a null value,
• missing if any element in the input array is missing,
• null if any element in the input array is null but no element in the input array is missing,
• any other non-array input value or non-integer element in the input array will cause a type error.
• Example:

codepoint_to_string([72, 101, 108, 108, 111, 32, 65, 83, 84, 69, 82, 73, 88, 33]);

• The expected result is:

"Hello ASTERIX!"

substr

• Syntax:

substr(string, offset[, length])

• Returns the substring from the given string string based on the given start offset offset with the optional length.

• Arguments:
• string : a string to be extracted,
• offset : an tinyint/smallint/integer/bigint value as the starting offset of the substring in string,
• length : (Optional) an an tinyint/smallint/integer/bigint value as the length of the substring.
• Return Value:
• a string that represents the substring,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• a type error will be raised if:
• the first argument is any other non-string value,
• or, the second argument is not a tinyint, smallint, integer, or bigint,
• or, the third argument is not a tinyint, smallint, integer, or bigint if the argument is present.
• Example:

substr("test string", 6, 3);

• The expected result is:

"str"

substring_before

• Syntax:

substring_before(string, string_pattern)

• Returns the substring from the given string string before the given pattern string_pattern.

• Arguments:
• string : a string to be extracted.
• string_pattern : a string pattern to be searched.
• Return Value:
• a string that represents the substring,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error.
• Example:

substring_before(" like iphone", "iphone");

• The expected result is:

" like "

substring_after

• Syntax:

substring_after(string, string_pattern);

• Returns the substring from the given string string after the given pattern string_pattern.

• Arguments:
• string : a string to be extracted.
• string_pattern : a string pattern to be searched.
• Return Value:
• a string that represents the substring,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error.
• Example:

substring_after(" like iphone", "iph");

• The expected result is:

"one"

trim

• Syntax:

trim(string[, chars]);

• Returns a new string with all leading characters that appear in chars removed. By default, white space is the character to trim.

• Arguments:
• string : a string to be trimmed,
• chars : a string that contains characters that are used to trim.
• Return Value:
• a trimmed, new string,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error.
• Example:

trim("i like iphone", "iphoen");

• The expected result is:

" like "

upper

• Syntax:

upper(string)

• Converts a given string string to its uppercase form.

• Arguments:
• string : a string to be converted.
• Return Value:
• a string as the uppercase form of the given string,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-string input value will cause a type error.
• Example:

upper("hello")

• The expected result is:

"HELLO"

Binary Functions

parse_binary

• Syntax:

parse_binary(string, encoding)

• Creates a binary from an string encoded in encoding format.

• Arguments:
• string : an encoded string,
• encoding : a string notation specifies the encoding type of the given string. Currently we support hex and base64 format.
• Return Value:
• a binary that is decoded from the given string,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error.
• Example:

[ parse_binary(“ABCDEF0123456789”,“hex”), parse_binary(“abcdef0123456789”,“HEX”), parse_binary(‘QXN0ZXJpeAE=’,“base64”) ];

• The expected result is:

[ hex(“ABCDEF0123456789”), hex(“ABCDEF0123456789”), hex(“4173746572697801”) ]

print_binary

• Syntax:

print_binary(binary, encoding)

• Prints a binary to the required encoding string format.

• Arguments:
• binary : a binary data need to be printed.
• encoding : a string notation specifies the expected encoding type. Currently we support hex and base64 format.
• Return Value:
• a string that represents the encoded format of a binary,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-string input value will cause a type error.
• Example:

[ print_binary(hex("ABCDEF0123456789"), "base64"), print_binary(base64("q83vASNFZ4k="), "hex") ]

• The expected result are:

[ "q83vASNFZ4k=", "ABCDEF0123456789" ]

binary_length

• Syntax:

binary_length(binary)

• Returns the number of bytes storing the binary data.

• Arguments:
• binary : a binary value to be checked.
• Return Value:
• an bigint that represents the number of bytes,
• missing if the argument is a missing value,
• null if the argument is a null value,
• any other non-binary input value will cause a type error.
• Example:

binary_length(hex("00AA"))

• The expected result is:

2

sub_binary

• Syntax:

sub_binary(binary, offset[, length])

• Returns the sub binary from the given binary based on the given start offset with the optional length.

• Arguments:
• binary : a binary to be extracted,
• offset : a tinyint, smallint, integer, or bigint value as the starting offset of the sub binary in binary,
• length : (Optional) a tinyint, smallint, integer, or bigint value as the length of the sub binary.
• Return Value:
• a binary that represents the sub binary,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• a type error will be raised if:
• the first argument is any other non-binary value,
• or, the second argument is any other non-integer value,
• or, the third argument is any other non-integer value, if it is present.
• Example:

sub_binary(hex("AABBCCDD"), 4);

• The expected result is

hex("DD")

binary_concat

• Syntax:

binary_concat(array)

• Concatenates a binary array or multiset into a single binary.

• Arguments:
• array : an array or multiset of binaries (could be null or missing) to be concatenated.
• Return Value :
• the concatenated binary value,
• missing if the argument is a missing value,
• null if the argument is a null value,
• missing if any element in the input array is missing,
• null if any element in the input array is null but no element in the input array is missing,
• any other non-array input value or non-binary element in the input array will cause a type error.
• Example:

binary_concat([hex(“42”), hex(""), hex(‘42’)]);

• The expected result is

hex(“4242”)

Spatial Functions

create_point

• Syntax:

create_point(x, y)

• Creates the primitive type point using an x and y value.

• Arguments:
• x : a double that represents the x-coordinate,
• y : a double that represents the y-coordinate.
• Return Value:
• a point representing the ordered pair (x, y),
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-double input value will cause a type error.
• Example:

{ "point": create_point(30.0,70.0) };

• The expected result is:

{ "point": point("30.0,70.0") }

create_line

• Syntax:

create_line(point1, point2)

• Creates the primitive type line using point1 and point2.

• Arguments:
• point1 : a point that represents the start point of the line.
• point2 : a point that represents the end point of the line.
• Return Value:
• a spatial line created using the points provided in point1 and point2,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-point input value will cause a type error.
• Example:

{ "line": create_line(create_point(30.0,70.0), create_point(50.0,90.0)) };

• The expected result is:

{ "line": line("30.0,70.0 50.0,90.0") }

create_rectangle

• Syntax:

create_rectangle(point1, point2)

• Creates the primitive type rectangle using point1 and point2.

• Arguments:
• point1 : a point that represents the lower_left point of the rectangle.
• point2 : a point that represents the upper_right point of the rectangle.
• Return Value:
• a spatial rectangle created using the points provided in point1 and point2,
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• any other non-point input value will cause a type error.
• Example:

{ "rectangle": create_rectangle(create_point(30.0,70.0), create_point(50.0,90.0)) };

• The expected result is:

{ "rectangle": rectangle("30.0,70.0 50.0,90.0") }

create_circle

• Syntax:

• Creates the primitive type circle using point and radius.

• Arguments:
• point : a point that represents the center of the circle.
• radius : a double that represents the radius of the circle.
• Return Value:
• a spatial circle created using the center point and the radius provided in point and radius.
• missing if any argument is a missing value,
• null if any argument is a null value but no argument is a missing value,
• a type error will be raised if:
• the first argument is any other non-point value,
• or, the second argument is any other non-double value.
• Example:

{ "circle": create_circle(create_point(30.0,70.0), 5.0) }

• The expected result is:

{ "circle": circle("30.0,70.0 5.0") }

create_polygon

• Syntax:

create_polygon(array)

• Creates the primitive type polygon using the double values provided in the argument array. Each two consecutive double values represent a point starting from the first double value in the array. Note that at least six double values should be specified, meaning a total of three points.

• Arguments:
• array : an array of doubles representing the points of the polygon.
• Return Value:
• a polygon, represents a spatial simple polygon created using the points provided in array.
• missing if the argument is a missing value,
• null if the argument is a null value,
• missing if any element in the input array is missing,
• null if any element in the input array is null but no element in the input array is missing,
• any other non-array input value or non-double element in the input array will cause a type error.
• Example:

{ "polygon": create_polygon([1.0,1.0,2.0,2.0,3.0,3.0,4.0,4.0]) };