叙述関数
REGEXP_CONTAINS(column, string_pattern)
Returns True if the string value contains the regular expression pattern.
- column: The column expression.
- string_pattern: The regular expression to match.
REGEXP_LIKE(column, string_pattern)
Returns True if the string value matches the regular expression pattern.
- column: The column expression.
- string_pattern: The regular expression to match.
REGEXP_POSITION(column, string_pattern)
Returns first position of the regular expression pattern within the string, or -1.
- column: The column expression.
- string_pattern: The regular expression to match.
REGEXP_REPLACE(column, string_pattern, string_replace [, integer_n])
Returns new string with occurrences of pattern replaced with string_replace. If n is given, at most n replacements are performed.
- column: The column expression.
- string_pattern: The regular expression to match.
- string_replace: The value to replace the matched pattern.
- integer_n: The maximum number of replacements to make.
ISARRAY(column)
Returns True if expression is an array, otherwise returns MISSING, NULL or false.
- column: Any column expression.
ISATOM(column)
Returns True if expression is a Boolean, number, or string, otherwise returns MISSING, NULL or false.
- column: Any column expression.
ISBOOLEAN(column)
Returns True if expression is a Boolean, otherwise returns MISSING, NULL or false.
- column: Any column expression.
ISNUMBER(column)
Returns True if expression is a number, otherwise returns MISSING, NULL or false.
- column: Any column expression.
ISOBJECT(column)
Returns True if expression is an object, otherwise returns MISSING, NULL or false.
- column: Any column expression.
ISSTRING(column)
Returns True if expression is a string, otherwise returns MISSING, NULL or false.
- column: Any column expression.
TYPE(column)
Returns one of the following strings, based on the value of expression: missing, null, boolean, number, string, array, object, or binary.
- column: Any column expression.
ARRAY_AVG(column)
Returns arithmetic mean (average) of all the non-NULL number values in the array, or NULL if there are no such values.
- column: Any column expression.
ARRAY_CONTAINS(column, value)
Returns true if the array contains value.
- column: Any column expression.
- value: The value contained within the array.
ARRAY_COUNT(column)
Returns count of all the non-NULL values in the array, or zero if there are no such values.
- column: Any column expression.
ARRAY_LENGTH(column)
Returns the number of elements in the array.
- column: Any column expression.
ARRAY_MAX(column)
Returns the largest non-NULL, non-MISSING array element, in N1QL collation order.
- column: Any column expression.
ARRAY_MIN(column)
Returns smallest non-NULL, non-MISSING array element, in N1QL collation order.
- column: Any column expression.
ARRAY_POSITION(column, value)
Returns the first position of value within the array, or -1. Array position is zero-based, i.e. the first position is 0.
- column: Any column expression.
- value: The value contained within the array.
ARRAY_SUM(column)
Sum of all the non-NULL number values in the array, or zero if there are no such values.
- column: Any column expression.
GREATEST(column1, column2 [,column3 [,column4]])
Largest non-NULL, non-MISSING value if the values are of the same type; otherwise NULL.
- column1: Any column expression.
- column2: Any column expression.
- column3: Any column expression.
- column4: Any column expression.
LEAST(column1, column2 [,column3 [,column4]])
Returns smallest non-NULL, non-MISSING value if the values are of the same type, otherwise returns NULL.
- column1: Any column expression.
- column2: Any column expression.
- column3: Any column expression.
- column4: Any column expression.
IFMISSING(column1, column2 [,column3 [,column4]])
Returns the first non-MISSING value.
- column1: Any column expression.
- column2: Any column expression.
- column3: Any column expression.
- column4: Any column expression.
IFMISSINGORNULL(column1, column2 [,column3 [,column4]])
Returns first non-NULL, non-MISSING value.
- column1: Any column expression.
- column2: Any column expression.
- column3: Any column expression.
- column4: Any column expression.
IFNULL(column1, column2 [,column3 [,column4]])
Returns first non-NULL value. Note that this function might return MISSING if there is no non-NULL value.
- column1: Any column expression.
- column2: Any column expression.
- column3: Any column expression.
- column4: Any column expression.
MISSINGIF(column1, column2)
Returns MISSING if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
- column1: Any column expression.
- column2: Any column expression.
NULLIF(column1, column2)
Returns NULL if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
- column1: Any column expression.
- column2: Any column expression.
IFINF(column1, column2 [,column3 [,column4]])
Returns first non-MISSING, non-Inf number. Returns MISSING or NULL if a non-number input is encountered first.
- column1: Any column expression.
- column2: Any column expression.
- column3: Any column expression.
- column4: Any column expression.
IFNAN(column1, column2 [,column3 [,column4]])
Returns first non-MISSING, non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.
- column1: Any column expression.
- column2: Any column expression.
- column3: Any column expression.
- column4: Any column expression.
IFNANORINF(column1, column2 [,column3 [,column4]])
Returns first non-MISSING, non-Inf, or non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.
- column1: Any column expression.
- column2: Any column expression.
- column3: Any column expression.
- column4: Any column expression.
NANIF(column1, column2 [,column3 [,column4]])
Returns NaN if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
- column1: Any column expression.
- column2: Any column expression.
- column3: Any column expression.
- column4: Any column expression.
NEGINFIF(column1, column2 [,column3 [,column4]])
Returns NegInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
- column1: Any column expression.
- column2: Any column expression.
- column3: Any column expression.
- column4: Any column expression.
POSINFIF(column1, column2 [,column3 [,column4]])
Returns PosInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.
- column1: Any column expression.
- column2: Any column expression.
- column3: Any column expression.
- column4: Any column expression.
CLOCK_MILLIS()
Returns system clock at function evaluation time, as UNIX milliseconds. Varies during a query.
CLOCK_STR([string_fmt])
Returns system clock at function evaluation time, as a string in a supported format. Varies during a query.
- string_fmt: The datetime format to return the system clock in.
DATE_ADD_MILLIS(column, integer_n, string_part)
Performs date arithmetic, and returns result of computation. n and part are used to define an interval or duration, which is then added (or subtracted) to the UNIX time stamp, returning the result.
- column: Any column expression.
- integer_n: The number of string_part's to add to the column value.
- string_part: The part to add integer_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
DATE_ADD_STR(column, integer_n, string_part)
Performs date arithmetic. n and part are used to define an interval or duration, which is then added (or subtracted) to the date string in a supported format, returning the result.
- column: Any column expression.
- integer_n: The number of string_part's to add to the column value.
- string_part: The part to add integer_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
DATE_DIFF_MILLIS(column1, column2, string_part)
Performs date arithmetic. Returns the elapsed time between two UNIX time stamps as an integer whose unit is part.
- column1: Any column expression.
- column2: Any column expression.
- string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
DATE_DIFF_STR(column1, column2, string_part)
Performs date arithmetic. Returns the elapsed time between two date strings in a supported format, as an integer whose unit is part.
- column1: Any column expression.
- column2: Any column expression.
- string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
DATE_PART_MILLIS(column1, string_part [, tz])
Returns date part as an integer. The date expression is a number representing UNIX milliseconds, and part is one of the following date part strings.
- column1: Any column expression.
- string_part: The component of the date to extract. Available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day_of_year, day_of_week, iso_week, iso_year, iso_dow, timezone, timezone_hour, and timezone_minute.
- tz: The timezone to convert the local time to. Default to the system timezone if not specified. If an incorrect time zone is provided, the null is returned.
DATE_PART_STR(column1, string_part)
Returns date part as an integer. The date expression is a string in a supported format, and part is one of the supported date part strings.
- column1: Any column expression.
- string_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day_of_year, day_of_week, iso_week, iso_year, iso_dow, timezone, timezone_hour, and timezone_minute.
DATE_TRUNC_MILLIS(column1, string_part)
Returns UNIX time stamp that has been truncated so that the given date part string is the least significant.
- column1: Any column expression.
- string_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
DATE_TRUNC_STR(column1, string_part)
Returns ISO 8601 time stamp that has been truncated so that the given date part string is the least significant.
- column1: Any column expression.
- string_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.
MILLIS(column1)
Returns date that has been converted in a supported format to UNIX milliseconds.
- column1: Any column expression.
STR_TO_MILLIS(column1)
Returns date that has been converted in a supported format to UNIX milliseconds.
- column1: Any column expression.
MILLIS_TO_STR(column [, string_fmt])
Returns the string in the supported format to which the UNIX milliseconds has been converted.
- column1: Any column expression.
- string_fmt: The datetime format to return the system clock in.
MILLIS_TO_UTC(column [, string_fmt])
Returns the UTC string to which the UNIX time stamp has been converted in the supported format.
- column1: Any column expression.
- string_fmt: The datetime format to return the system clock in.
MILLIS_TO_TZ(column, string_tzname [, string_fmt])
Converts the UNIX time stamp to a string in the named time zone, and returns the string.
- column1: Any column expression.
- string_tzname: The time zone name.
- string_fmt: The datetime format to return the system clock in.
NOW_MILLIS()
Returns statement time stamp as UNIX milliseconds; does not vary during a query.
NOW_STR([string_fmt])
Returns statement time stamp as a string in a supported format; does not vary during a query.
- string_fmt: The datetime format to return the timestamp in.
STR_TO_UTC(column1)
Converts the ISO 8601 time stamp to UTC.
- column1: Any column expression.
STR_TO_ZONE_NAME(column, string_tzname)
Converts the supported time stamp string to the named time zone.
- column1: Any column expression.
- string_tzname: The time zone name.
BASE64(expression)
Returns base64 encoding of expression.
- expression: Any column or literal expression.
ABS(expression)
Returns absolute value of the number.
- expression: Any column or literal expression.
ACOS(expression)
Returns arccosine in radians.
- expression: Any column or literal expression.
ASIN(expression)
Returns arcsine in radians.
- expression: Any column or literal expression.
ATAN(expression)
Returns arctangent in radians.
- expression: Any column or literal expression.
ATAN2(expression1, expression2)
Returns arctangent of expression2/expression1.
- expression1: Any column or literal expression.
- expression2: Any column or literal expression.
CEIL(expression)
Returns smallest integer not less than the number.
- expression: Any column or literal expression.
COS(expression)
Returns cosine.
- expression: Any column or literal expression.
DEGREES(expression)
Returns radians to degrees.
- expression: Any column or literal expression.
E()
Base of natural logarithms.
EXP(expression)
Returns e^expression.
- expression: Any column or literal expression.
LN(expression)
Returns log base e.
- expression: Any column or literal expression.
LOG(expression)
Returns log base 10.
- expression: Any column or literal expression.
FLOOR(expression)
Largest integer not greater than the number.
- expression: Any column or literal expression.
PI()
Returns PI.
POWER(expression1, expression2)
Returns expression1^expression2.
- expression1: Any column or literal expression.
- expression2: Any column or literal expression.
RADIANS(expression)
Returns degrees to radians.
- expression: Any column or literal expression.
RANDOM([expression])
Returns pseudo-random number with optional seed.
- expression: Any column or literal expression.
ROUND(expression [, integer_digits])
Rounds the value 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.
- expression: Any column or literal expression.
- integer_digits: The number of digits to round to.
SIGN(expression)
Valid values: -1, 0, or 1 for negative, zero, or positive numbers respectively.
- expression: Any column or literal expression.
SIN(expression)
Returns sine.
- expression: Any column or literal expression.
SQRT(expression)
Returns square root.
- expression: Any column or literal expression.
TAN(expression)
Returns tangent.
- expression: Any column or literal expression.
TRUNC(expression [, integer_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.
- expression: Any column or literal expression.
- integer_digits: The number of digits to truncate.
CONTAINS(column, string_substring)
True if the string contains the substring.
- column: Any column or literal expression.
- string_substring: The substring to search for.
INITCAP(column)
Converts the string so that the first letter of each word is uppercase and every other letter is lowercase.
- column: Any column or literal expression.
LENGTH(column)
Returns length of the string value.
- column: Any column or literal expression.
LOWER(column)
Returns lowercase of the string value.
- column: Any column or literal expression.
LTRIM(column [, string_chars])
Returns string with all leading chars removed. White space by default.
- column: Any column or literal expression.
- string_chars: The leading characters to remove.
POSITION(column, string_substring)
Returns the first position of the substring within the string, or -1. The position is zero-based, i.e., the first position is 0.
- column: Any column or literal expression.
- string_substring: The substring to search for.
REPEAT(column, integer_n)
Returns string formed by repeating expression n times.
- column: Any column or literal expression.
- integer_n: The number of times to repeat column.
REPLACE(column, string_substring, string_replace [, integer_n])
Returns string with all occurrences of substr replaced with repl. If n is given, at most n replacements are performed.
- column: The column expression.
- string_substring: The regular expression to match.
- string_replace: The value to replace the matched pattern.
- integer_n: The maximum number of replacements to make.
RTRIM(column [, string_chars])
Returns string with all trailing chars removed. White space by default.
- column: Any column or literal expression.
- string_chars: The trailing characters to remove.
SPLIT(column [, string_sep])
Splits the string into an array of substrings separated by string_sep. If string_sep is not given, any combination of white space characters is used.
- column: Any column or literal expression.
- string_sep: The separator to split column on.
SUBSTR(column, integer_position [, integer_length])
Returns substring from the integer position of the given length, or to the end of the string. The position is zero-based, i.e. the first position is 0. If position is negative, it is counted from the end of the string; -1 is the last position in the string.
- column: Any column or literal expression.
- integer_position: The starting position.
- integer_length: The total length of the substring to retrieve.
TRIM(column [, string_chars])
Returns string with all leading and trailing chars removed. White space by default.
- column: Any column or literal expression.
- string_chars: The leading and trailing characters to remove.
UPPER(column)
Returns uppercase of the string value.
- column: Any column or literal expression.
TOARRAY(column)
Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays are themselves; All other values are wrapped in an array.
- column: Any column expression.
TOATOM(column)
Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays of length 1 are the result of TOATOM() on their single element; Objects of length 1 are the result of TOATOM() on their single value; Booleans, numbers, and strings are themselves; All other values are NULL.
- column: Any column expression.
TOBOOLEAN(column)
Returns array as follows: MISSING is MISSING; NULL is NULL; False is false; Numbers +0, -0, and NaN are false; Empty strings, arrays, and objects are false; All other values are true.
- column: Any column expression.
TONUMBER(column)
Returns array as follows: MISSING is MISSING; NULL is NULL; False is 0; True is 1; Numbers are themselves; Strings that parse as numbers are those numbers; All other values are NULL.
- column: Any column expression.
TOOBJECT(column)
Returns array as follows: MISSING is MISSING; NULL is NULL; Objects are themselves; All other values are the empty object.
- column: Any column expression.
TOSTRING(column)
Returns array as follows: MISSING is MISSING; NULL is NULL; False is "false"; True is "true"; Numbers are their string representation; Strings are themselves; All other values are NULL.
- column: Any column expression.