Functions for MariaDB Xpand 23.09

This page is part of MariaDB's Documentation.

The parent of this page is: Reference for MariaDB Xpand 23.09

Topics on this page:

Overview

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