Functions for MariaDB Xpand 6.1

Overview

MariaDB Xpand includes built-in functions to perform common programming tasks, such as working with strings, numbers, temporal data; Regular Expression (regex) matching; and other data manipulation tasks performed on query result-sets.

Function

Description

ABS()

Returns the absolute (non-negative) value of the given argument

ACOS()

Returns the arc cosine of the given argument

ADD()

Return the sum of two numbers

ADD_DATE_INTERVAL()

Returns a DATE or DATETIME value that has been modified by count date units

ADD_TIME_INTERVAL()

Returns a TIME or DATETIME value that has count time units added

ADDDATE()

Works as a synonym for DATE_ADD() with one exception: the second argument can be a numeric count of days instead of an INTERVAL expression

ADDTIME()

Returns the sum of two TIME or DATETIME expressions

AES_DECRYPT()

Decrypts data using the AES algorithm with the given key

AES_ENCRYPT()

Encrypts data using the AES algorithm with the given key

ALERT()

Send an email alert

ANDBITS()

Returns a bitwise "and" of two numbers

ASCII()

Returns the numeric ASCII value of the leftmost character of the given argument

ASIN()

Returns the arc sine of the given argument

ATAN()

Returns the arc tangent angle based on a tangent slope's ratio

ATAN2()

Returns the arc tangent angle based on the length of the opposite and adjacent sides

AVG()

Returns the average of a set of aggregated values

BIN()

Returns a string representation of the binary value of the given BIGINT number

BIT_AND()

Returns a bitwise "and" operation across a set of aggregated numbers

BIT_COUNT()

Returns the number of bits set in the given argument

BIT_LENGTH()

Returns the length of a string in bits

BIT_OR()

Returns the bitwise "or" operation across a set of numbers

BIT_XOR()

Returns the bitwise exclusive-or operation across a set of numbers

CAPITALIZE()

Return a string that starts with an uppercase letter instead of lowercase

CAST()

Takes a value of one type and converts it to the value of another of type. See CONVERT.

CAST_NULL_ON_ERR()

CEIL()

See CEILING()

CEILING()

Returns the nearest integer value, rounding upwards

CHAR()

Returns a string consisting of characters defined by the given code values

CHAR_LENGTH()

Returns the number of characters in the given string, counting each multi-byte char as 1 character

CHARACTER_LENGTH()

See CHAR_LENGTH()

CMP()

Returns -1, 0, or 1 indicating the order of two values

COALESCE()

Returns the first non-NULL value in the given list and NULL when all values are NULL

COMPRESS()

Compresses the given string and returns the binary string result

CONCAT()

Concatenates and returns the given string arguments

CONCAT_WS()

Concatenates and returns the given strings using the specified separator

CONNECTION_ID()

Returns the thread ID for the given connection

CONV()

Converts the given numbers from the first base argument to the second base argument

CONVERT()

Converts the given value from one character set encoding to another

CONVERT_TZ()

Converts the given DATETIME value from the first time zone to the second time zone

COS()

Returns the cosine of an angle, specified in radians

COT()

Returns the cotangent of the given argument

COUNT()

Returns the number of values in the given expression

CRC32()

Computes a cyclic redundancy check value and returns a 32-bit unsigned value

CURDATE()

Returns the current date in the currently active timezone

CURRENT_DATE()

See CURDATE()

CURRENT_ROLE()

See CURRENT_USER()

CURRENT_TIME()

See CURTIME()

CURRENT_TIMESTAMP()

See NOW()

CURRENT_USER()

Returns the user and host of the current client connection

CURTIME()

Returns the current time of day in the currently active timezone

DATABASE()

Returns the name of the default (current) database

DATE()

Returns the date component of the given DATETIME expression

DATE_ADD()

Performs arithmetic operations on the given date value, adding an INTERVAL expression in the specified units

DATE_FORMAT()

Returns a date value as a string using the given format

DATE_SUB()

Returns a date with an INTERVAL expression subtracted

DATEDIFF()

Calculates the differences in days between two DATE arguments

DATETIME_TRUNC()

Returns a DATETIME with the fractional seconds truncated to the requested number of decimal places

DAY()

See DAYOFMONTH()

DAYNAME()

Returns the name of the day of the week for the given date

DAYOFMONTH()

Returns the day of the month in the given date

DAYOFWEEK()

Returns the index for the day of the week of the given DATE

DAYOFYEAR()

Returns the day of the year for the given date, in a range of 1 to 366

DECODE()

Decrypts the string using the given key

DEGREES()

Converts the given argument from radians to degrees

DIVFLT()

Return the floating point value of one number divided by another

DIVINT()

Return the integer value of one number divided by another

ELT()

Returns the string that corresponds to the given numeric position in a list of strings

ENCODE()

Encrypts a string using the given key

ENCRYPT()

Encrypts a string with the Unix crypt() system call

EQ()

Returns a boolean indicating if two values are equivalent

EXP()

Returns the value of e, the base of natural logarithms, raised to the given power

EXPORT_SET()

Returns a string representation of a bits argument using the given values for on and off

FASTRAND()

Returns a non-negative 64-bit random integer

FIELD()

Returns the position of the first argument's string in the subsequent string arguments

FIND_IN_SET()

Returns the position of a string in a set of comma-separated substrings

FLOOR()

Returns the nearest integer value, rounding downwards

FORMAT()

Returns a formatted number rounded to the specified number of decimal places

FOUND_ROWS()

Returns the number of rows found by the most recent successful SELECT statement

FROM_BASE64()

Returns the decoded value as binary string of a base-64 encoded string

FROM_DAYS()

Returns the DATE for a day number based on the Gregorian calendar

FROM_UNIXTIME()

Returns a date-time value based on a Unix seconds-since-epic numeric value

GET_FORMAT()

Returns a format string that can be used for rendering dates and time values

GET_LOCK()

Attempt to create a user-level lock and return the success status as a boolean

GREATEST()

Returns the greatest argument from the argument list

GROUP_CONCAT()

Returns a concatenated string formed from the arguments

GT()

Returns a boolean indicating if one value is greater than another

GTEQ()

Returns a boolean indicating if one value is greater or equal to than another

HEX()

Returns a hexadecimal string representation of the argument

HOUR()

Returns the hour for a TIME or DATETIME argument

IDENTICAL()

Returns a boolean indicating if two values are equal

IF()

Returns one of two values based on a truth expression

IFNULL()

Returns an alternate value in place of NULL

INET_ATON()

Returns a numeric form of the IPv4 address in string form

INET_NTOA()

Returns the string form of an IPv4 address in numeric form

INSERT()

Inserts a string in another string at the specified position

INSTR()

Returns the position of the first occurrence of a substring in another string

INTERVAL()

Returns the index of the last argument that is less than the first argument or is NULL

IS_FREE_LOCK()

Returns a boolean to indicate if a named user-level lock is unlocked

IS_USED_LOCK()

Returns the connection identifier of the client session that holds a named user-level lock

ISNOTNULL()

Returns a boolean that indicates if the argument is not NULL

ISNULL()

Returns a boolean that indicates if the argument is NULL

JSON_ARRAY()

Returns a JSON array created from the argument list

JSON_CONTAINS_PATH()

Returns a boolean to indicate whether a JSON object contains at least one of the specified JSON paths

JSON_DEPTH()

Returns the maximum depth in terms of inner JSON levels in the JSON data

JSON_EXTRACT()

Returns extracted data from some JSON data by applying a path selector

JSON_KEYS()

Returns the keys of a JSON object as a JSON array

JSON_LENGTH()

Returns the length for an item of JSON data

JSON_OBJECT()

Returns a JSON object created from key/value pairs

JSON_QUOTE()

Returns a double-quoted JSON string from a string argument

JSON_SEARCH()

Returns the JSON path to a query or search string within a JSON object

JSON_TYPE()

Returns the type of an item of JSON data

JSON_UNQUOTE()

Transforms a JSON string into the string value that it represents

JSON_VALID()

Returns a boolean value to indicate if a string is valid JSON data

LAST_DAY()

Returns a date string representing the last day of the month specified by a DATE or DATETIME argument

LAST_INSERT_ID()

Returns the value of the most recent auto-generated AUTO_INCREMENT column

LCASE()

See LOWER()

LEAST()

Returns the argument with the least value from a list of arguments

LEFT()

Returns the requested number of leftmost characters in a string

LENGTH()

Returns the byte length of a string counting each multi-byte char as multiple bytes

LIKE()

Returns a boolean indicating if a string value is matched by a wildcard value

LN()

Returns the natural logarithm of the argument

LOCALTIME()

See NOW()

LOCALTIMESTAMP()

See NOW()

LOCATE()

Returns the position of the first occurrence of a substring within a string

LOG()

Returns the logarithm of a value in either base-e or a specified base

LOG2()

Returns the base-2 logarithm of the argument

LOG10()

Returns the base-10 logarithm of the argument

LOWER()

Returns a string converted to lowercase

LPAD()

Returns a string left-padded with the specified characters

LT()

Returns a boolean indicating if one value is less than another

LTEQ()

Returns a boolean indicating if one value is less than or equal to another

LTRIM()

Returns a string with certain leading characters removed

MAKE_SET()

Returns a string of comma-separated bit names that are enabled in a bit value

MAKEDATE()

Returns a date constructed from the year and day-of-year arguments

MAKETIME()

Returns a time value constructed from separate hour, minute, and second values

MAX()

Returns the maximum value from the aggregated values

MD5()

Returns the MD5 checksum for the given string argument

MICROSECOND()

Returns the microseconds from the TIME or DATETIME expression as a number

MID()

See SUBSTRING()

MIN()

Returns the minimum value from the aggregated values

MINUTE()

Returns the minutes for a time

MOD()

Returns the remainder of a division

MONTH()

Returns the month number of a date

MONTHNAME()

Returns the month name of a date

MUL()

Returns the product of two numbers multiplied together

NAME_CONST()

Returns a value with an associated column name

NEQ()

Returns a boolean indicating if two values are not equivalent

NOT()

Returns a negated boolean expression

NOTBITS()

Returns a bitwise negated value

NOW()

Returns the current date and time in the currently active timezone

NULLEQ()

Returns a boolean indicating if two values are equivalent, even NULL values

NULLIF()

Returns NULL or the first argument based on an equality comparison of the two arguments

OCT()

Returns the octal value of a numerical argument

OCTET_LENGTH()

See LENGTH()

OLD_PASSWORD()

Returns an encrypted string using the old implementation of PASSWORD() (pre-MySQL 4.1)

ORBITS()

Returns a bitwise "or" of two numbers

ORD()

Returns the code value of the leftmost character in the string argument

PASSWORD()

Returns an encrypted string from a plaintext password

PERIOD_ADD()

Adds a number of months to a starting period and returns the result

PERIOD_DIFF()

Returns the difference in months between two periods

PI()

Returns an approximate value of pi

POW()

See POWER()

POWER()

Returns the value of the first argument raised to the power of the second argument

QUARTER()

Returns the quarter number for a date

QUOTE()

Returns a string that is enclosed in single quotes with properly escaped special characters

RADIANS()

Returns a value converted from degrees to radians

RAND()

Returns a random non-negative floating-point number less than 1

REGEXP()

Returns a boolean indicating if a string value is matched by a regular expression

RELEASE_LOCK()

Returns a boolean to indicate if a named user-level lock was released

REPEAT()

Returns a string repeated the specified number of times

REPLACE()

Replaces all occurrences of one string with another and returns the resulting string

REVERSE()

Returns a string with all the characters reversed

RIGHT()

Returns the requested number of rightmost characters in a string

ROUND()

Returns a number rounded to the requested number of decimal places

ROW_COUNT()

Returns the number of rows updated, inserted, or deleted by the preceding statement

RPAD()

Returns a string right-padded with the specified characters

RTRIM()

Returns a string with certain trailing characters removed

SCHEMA()

See DATABASE()

SEC_TO_TIME()

Returns the time value derived from the number of seconds since midnight

SECOND()

Returns the seconds from a given time

SESSION_USER()

See USER()

SHA()

See SHA1()

SHA1()

Returns an SHA-1 checksum hash as a hexadecimal string

SHA2()

Returns an SHA-2 checksum hash as a hexadecimal string

SHIFTL()

Returns a number with its bits shifted to the left

SHIFTR()

Returns a number with its bits shifted to the right

SIGN()

Returns the sign of the argument

SIN()

Returns the sine of an angle, specified in radians

SLEEP()

Sleeps for the requested number of seconds

SOUNDEX()

Returns the Soundex representation of a string

SPACE()

Returns a string consisting of the requested number of space characters

SQR()

Returns the square of a number

SQRT()

Returns the square root of a number

STD()

See STDDEV_POP()

STDDEV()

See STDDEV_POP()

STDDEV_POP()

Returns the population standard deviation of the aggregated values

STDDEV_SAMP()

Returns the sample standard deviation of the aggregated values

STR_TO_DATE()

Parses a string argument using the supplied format and returns a DATE, TIME, or DATETIME value

STR_TO_DATE_DATE()

Parses a string argument using the supplied format and returns a DATE value

STR_TO_DATE_TIME()

Parses a string argument using the supplied format and returns a TIME or DATETIME value

STRCMP()

Returns -1, 0, or 1 indicating the order of two string arguments

SUB()

Returns the second argument subtracted from the first argument

SUB_DATE_INTERVAL()

Returns a date with a count of units subtracted

SUB_TIME_INTERVAL()

Returns a TIME or DATETIME value that has count time units subtracted

SUBDATE()

Works as a synonym for DATE_SUB() with one exception: the second argument can be a numeric count of days instead of an INTERVAL expression

SUBSTR()

See SUBSTRING()

SUBSTRING()

Returns a substring of the first argument given a character position and optional length

SUBSTRING_INDEX()

Returns a substring that is deliniated by finding one or more delimiter strings

SUBTIME()

Subtracts a TIME from a TIME or DATETIME and returns the result

SUM()

Returns the sum of the aggregated values

SYSDATE()

Returns the system date and time

SYSDATE_TZ()

Returns the system date and time in the requested time zone

SYSTEM_USER()

Returns the current authenticating username and hostname

TAN()

Returns the tangent of an angle, specified in radians

TIME()

Returns the time part of a time-containing expression

TIME_FORMAT()

Returns a time string in the specified format

TIME_TO_SEC()

Returns a time value converted into a count of seconds since midnight

TIMEDIFF()

Returns the difference between two TIME arguments or two DATETIME arguments

TIMESTAMP()

Return a DATETIME string based on a datetime expression with an optional time offset

TIMESTAMPADD()

Adds the given number of units to the DATE or DATETIME argument and returns the result

TIMESTAMPDIFF()

Returns an integer count of how far apart the date arguments are in the requested units

TO_BASE64()

Returns a base-64 encoded representation of a string

TO_DAYS()

Returns the number of days since year 0 for a given date

TRIM()

Returns the provided string with its leading and/or trailing space characters removed

TRUNCATE()

Returns a number truncated to the requested number of decimal places

TYPEOF()

Returns a string describing the type of a value

UCASE()

See UPPER()

UNCOMPRESS()

Uncompresses and returns the string argument

UNCOMPRESSED_LENGTH()

Returns the uncompressed length of the given compressed string

UNHEX()

Returns a binary string based on one or more pairs of hexadecimal digits

UNIX_TIMESTAMP()

Returns a time as a number of seconds since the Unix epoch began

UNQUOTE()

Returns a string with removed surrounding quotes and removed backslash escapes

UPPER()

Returns a string converted to uppercase

USER()

Returns the current authenticating username and hostname

UTC_DATE()

Returns the current date in the UTC timezone

UTC_TIME()

Returns the current time of day in the UTC timezone

UTC_TIMESTAMP()

Returns the current date and time in the UTC timezone

UUID()

Returns a unique value of Universal Unique Identifier

UUID_SHORT()

Returns a "short" universal identifier as a 64-bit unsigned integer

VALUES()

Used in the UPDATE clause of an INSERT... ON DUPLICATE KEY UPDATE statement to refer to a column's inserted value.

VAR_POP()

Returns the population variance across a set of aggregated values

VAR_SAMP()

Returns the sample variance across a set of aggregated values

VARIANCE()

See VAR_POP()

VERSION()

Returns the version of the MariaDB server as a string

WEEK()

Returns the week number for a date

WEEKDAY()

Returns the week day index for a date

WEEKOFYEAR()

Returns the calendar-week number for a date

XORBITS()

Returns a bitwise exclusive "or" of two numbers

YEAR()

Returns the year for the given date

YEARWEEK()

Returns the year and week number for a date