W3cubDocs

/MariaDB

Function and Operator Reference

Name Description
+ Addition operator
/ Division operator
* Multiplication operator
% Modulo operator. Returns the remainder of N divided by M
- Subtraction operator
!= Not equals
< Less than
<= Less than or equal
<=> NULL-safe equal
= Equal
> Greater than
>= Greater than or equal
& Bitwise AND
<< Shift left
>> Shift right
^ Bitwise XOR
! Logical NOT
&& Logical AND
XOR Logical XOR
|| Logical OR
| Bitwise OR
:= Assignment operator
= Assignment and comparison operator
~ Bitwise NOT
ABS Returns an absolute value
ACOS Returns an arc cosine
ADDDATE Add days or another interval to a date
ADDTIME Adds a time to a time or datetime
AES_DECRYPT Decryption data encrypted with AES_ENCRYPT
AES_ENCRYPT Encrypts a string with the AES algorithm
AREA Synonym for ST_AREA
AsBinary Synonym for ST_AsBinary
ASCII Numeric ASCII value of leftmost character
ASIN Returns the arc sine
AsText Synonym for ST_AsText
AsWKB Synonym for ST_AsBinary
AsWKT Synonym for ST_AsText
ATAN Returns the arc tangent
ATAN2 Returns the arc tangent of two variables
AVG Returns the average value
BENCHMARK Executes an expression repeatedly
BETWEEN AND True if expression between two values
BIN Returns binary value
BINARY OPERATOR Casts to a binary string
BINLOG_GTID_POS Returns a string representation of the corresponding GTID position
BIT_AND Bitwise AND
BIT_COUNT Returns the number of set bits
BIT_LENGTH Returns the length of a string in bits
BIT_OR Bitwise OR
BIT_XOR Bitwise XOR
BOUNDARY Synonym for ST_BOUNDARY
BUFFER Synonym for ST_BUFFER
CASE Returns the result where value=compare_value or for the first condition that is true
CAST Casts a value of one type to another type
CEIL Synonym for CEILING()
CEILING Returns the smallest integer not less than X
CENTROID Synonym for ST_CENTROID
CHAR Function Returns string based on the integer values for the individual characters
CHARACTER_LENGTH Synonym for CHAR_LENGTH()
CHAR_LENGTH Length of the string in characters
CHARSET Returns the character set
CHR Returns a string consisting of the character given by the code values of the integer
COALESCE Returns the first non-NULL parameter
COERCIBILITY Returns the collation coercibility value
COLLATION Collation of the string argument
COLUMN_ADD Adds or updates dynamic columns
COLUMN_CHECK Checks if a dynamic column blob is valid
COLUMN_CREATE Returns a dynamic columns blob
COLUMN_DELETE Deletes a dynamic column
COLUMN_EXISTS Checks is a column exists
COLUMN_GET Gets a dynamic column value by name
COLUMN_JSON Returns a JSON representation of dynamic column blob data
COLUMN_LIST Returns comma-separated list
COMPRESS Returns a binary, compressed string
CONCAT Returns concatenated string
CONCAT_WS Concatenate with separator
CONNECTION_ID Connection thread ID
CONTAINS Whether one geometry contains another
CONVERT Convert a value from one type to another type
CONV Converts numbers between different number bases
CONVERT_TZ Converts a datetime from on time zone to another
CONVEXHULL Synonym for ST_CONVEXHULL
COS Returns the cosine
COT Returns the cotangent
COUNT Returns count of non-null values
COUNT DISTINCT Returns count of number of different non-NULL values
CRC32 Computes a cyclic redundancy check value
CROSSES Whether two geometries spatially cross
CUME_DIST Window function that returns the cumulative distribution of a given row
CURDATE Returns the current date
CURRENT_DATE Synonym for CURDATE()
CURRENT_ROLE Current role name
CURRENT_TIME Synonym for CURTIME()
CURRENT_TIMESTAMP Synonym for NOW()
CURRENT_USER Username/host that authenicated the current client
CURTIME Returns the current time
DATABASE Current default database
DATE FUNCTION Extracts the date portion of a datetime
DATEDIFF Difference in days between two date/time values
DATE_ADD Date arithmetic - addition
DATE_FORMAT Formats the date value according to the format string
DATE_SUB Date arithmetic - subtraction
DAY Synonym for DAYOFMONTH()
DAYNAME Return the name of the weekday
DAYOFMONTH Returns the day of the month
DAYOFWEEK Returns the day of the week index
DAYOFYEAR Returns the day of the year
DECODE Decrypts a string encoded with ENCODE()
DECODE_HISTOGRAM Returns comma separated numerics corresponding to a probability distribution represented by a histogram
DEFAULT Returns column default
DEGREES Converts from radians to degrees
DENSE_RANK Rank of a given row with identical values receiving the same result, no skipping
DES_DECRYPT Decrypts a string encrypted with DES_ENCRYPT()
DES_ENCRYPT Encrypts a string using the Triple-DES algorithm
DIMENSION Synonym for ST_DIMENSION
DISJOINT Whether the two elements do not intersect
DIV Integer division
ELT Returns the N'th element from a set of strings
ENCODE Encrypts a string
ENCRYPT Encrypts a string with Unix crypt()
ENDPOINT Synonym for ST_ENDPOINT
ENVELOPE Synonym for ST_ENVELOPE
EQUALS Indicates whether two geometries are spatially equal
EXP e raised to the power of the argument
EXPORT_SET Returns an on string for every bit set, an off string for every bit not set
ExteriorRing Synonym for ST_ExteriorRing
EXTRACT Extracts a portion of the date
EXTRACTVALUE Returns the text of the first text node matched by the XPath expression
FIELD Returns the index position of a string in a list
FIND_IN_SET Returns the position of a string in a set of strings
FLOOR Largest integer value not greater than the argument
FORMAT Formats a number
FOUND_ROWS Number of (potentially) returned rows
FROM_BASE64 Given a base-64 encoded string, returns the decoded result as a binary string
FROM_DAYS Returns a date given a day
FROM_UNIXTIME Returns a datetime from a Unix timestamp
GeomCollFromText Synonym for ST_GeomCollFromText
GeomCollFromWKB Synonym for ST_GeomCollFromWKB
GeometryCollectionFromText Synonym for ST_GeomCollFromText
GeometryCollectionFromWKB Synonym for ST_GeomCollFromWKB
GeometryFromText Synonym for ST_GeomFromText
GeometryFromWKB Synonym for ST_GeomFromWKB
GeomFromText Synonym for ST_GeomFromText
GeomFromWKB Synonym for ST_GeomFromWKB
GeometryN Synonym for ST_GeometryN
GEOMETRYCOLLECTION Constructs a WKB GeometryCollection
GeometryType Synonym for ST_GeometryType
GET_FORMAT Returns a format string
GET_LOCK Obtain LOCK
GLENGTH Length of a LineString value
GREATEST Returns the largest argument
GROUP_CONCAT Returns string with concatenated values from a group
HEX Returns hexadecimal value
HOUR Returns the hour
IF If expr1 is TRUE, returns expr2; otherwise returns expr3
IFNULL Check whether an expression is NULL
IN True if expression equals any of the values in the list
INTERVAL Index of the argument that is less than the first argument
INET6_ATON Given an IPv6 or IPv4 network address, returns a VARBINARY numeric value
INET6_NTOA Given an IPv6 or IPv4 network address, returns the address as a nonbinary string
INET_ATON Returns numeric value of IPv4 address
INET_NTOA Returns dotted-quad representation of IPv4 address
INSERT Function Replaces a part of a string with another string
INSTR Returns the position of a string withing a string
InteriorRingN Synonym for ST_InteriorRingN
INTERSECTS Indicates whether two geometries spatially intersect
IS Tests whether a boolean is TRUE, FALSE, or UNKNOWN
IsClosed Synonym for ST_IsClosed
IsEmpty Synonym for ST_IsEmpty
IS_FREE_LOCK Checks whether lock is free to use
IS_IPV4 Whether or not an expression is a valid IPv4 address
IS_IPV4_COMPAT Whether or not an IPv6 address is IPv4-compatible
IS_IPV4_MAPPED Whether an IPv6 address is a valid IPv4-mapped address
IS_IPV6 Whether or not an expression is a valid IPv6 address
IS NOT Tests whether a boolean value is not TRUE, FALSE, or UNKNOWN
IS NOT NULL Tests whether a value is not NULL
IS NULL Tests whether a value is NULL
ISNULL Checks if an expression is NULL
IsRing Synonym for ST_IsRing
IsSimple Synonym for ST_IsSimple
IS_USED_LOCK Check if lock is in use
JSON_ARRAY Returns a JSON array containing the listed values
JSON_ARRAY_APPEND Appends values to the end of the given arrays within a JSON document
JSON_ARRAY_INSERT Inserts a value into a JSON document
JSON_COMPACT Removes all unnecessary spaces so the json document is as short as possible
JSON_CONTAINS Whether a value is found in a given JSON document or at a specified path within the document
JSON_CONTAINS_PATH Indicates whether the given JSON document contains data at the specified path or paths
JSON_DEPTH Maximum depth of a JSON document
JSON_DETAILED Represents JSON in the most understandable way emphasizing nested structures
JSON_EXISTS Determines whether a specified JSON value exists in the given data
JSON_EXTRACT Extracts data from a JSON document
JSON_INSERT Inserts data into a JSON document
JSON_KEYS Returns keys from top-level value of a JSON object or top-level keys from the path
JSON_LENGTH Returns the length of a JSON document, or the length of a value within the document
JSON_LOOSE Adds spaces to a JSON document to make it look more readable
JSON_MERGE Merges the given JSON documents
JSON_OBJECT Returns a JSON object containing the given key/value pairs
JSON_QUERY Given a JSON document, returns an object or array specified by the path
JSON_QUOTE Quotes a string as a JSON value
JSON_REMOVE Removes data from a JSON document
JSON_REPLACE Replaces existing values in a JSON document
JSON_SEARCH Returns the path to the given string within a JSON document
JSON_SET Updates or inserts data into a JSON document
JSON_TYPE Returns the type of a JSON value
JSON_UNQUOTE Unquotes a JSON value, returning a string
JSON_VALID Whether a value is a valid JSON document or not
JSON_VALUE Given a JSON document, returns the specified scalar
LAST_DAY Returns the last day of the month
LAST_INSERT_ID Last inserted autoinc value
LAST_VALUE Returns the last value in a list
LASTVAL Get last value generated from a sequence
LCASE Synonym for [LOWER()
LEAST Returns the smallest argument
LEFT Returns the leftmost characters from a string
LENGTH Length of the string in bytes
LIKE Whether expression matches a pattern
LineFromText Synonym for ST_LineFromText
LineFromWKB Synonym for ST_LineFromWKB
LINESTRING Constructs a WKB LineString value from a number of WKB Point arguments
LineStringFromText Synonym for ST_LineFromText
LineStringFromWKB Synonym for ST_LineFromWKB
LN Returns natural logarithm
LOAD_FILE Returns file contents as a string
LOCALTIME Synonym for NOW()
LOCALTIMESTAMP Synonym for NOW()
LOCATE Returns the position of a substring in a string
LOG Returns the natural logarithm
LOG10 Returns the base-10 logarithm
LOG2 Returns the base-2 logarithm
LOWER Returns a string with all characters changed to lowercase
LPAD Returns the string left-padded with another string to a given length
LTRIM Returns the string with leading space characters removed
MAKE_SET Make a set of strings that matches a bitmask
MAKEDATE Returns a date given a year and day
MAKETIME Returns a time
MASTER_GTID_WAIT Wait until slave reaches the GTID position
MASTER_POS_WAIT Blocks until the slave has applied all specified updates
MATCH AGAINST Perform a fulltext search on a fulltext index
MAX Returns the maximum value
MBRContains Indicates one Minimum Bounding Rectangle contains another
MBRDisjoint Indicates whether the Minimum Bounding Rectangles of two geometries are disjoint
MBREqual Whether the Minimum Bounding Rectangles of two geometries are the same.
MBRIntersects Indicates whether the Minimum Bounding Rectangles of the two geometries intersect
MBROverlaps Whether the Minimum Bounding Rectangles of two geometries overlap.
MBRTouches Whether the Minimum Bounding Rectangles of two geometries touch.
MBRWithin Indicates whether one Minimum Bounding Rectangle is within another
MD5 MD5 checksum
MEDIAN Window function that returns the median value of a range of values
MICROSECOND Returns microseconds from a date or datetime
MID Synonym for SUBSTRING(str,pos,len)
MIN Returns the minimum value
MINUTE Returns a minute from 0 to 59
MLineFromText Constructs MULTILINESTRING using its WKT representation and SRID
MLineFromWKB Constructs a MULTILINESTRING
MOD Modulo operation. Remainder of N divided by M
MONTH Returns a month from 1 to 12
MONTHNAME Returns the full name of the month
MPointFromText Constructs a MULTIPOINT value using its WKT and SRID
MPointFromWKB Constructs a MULTIPOINT value using its WKB representation and SRID
MPolyFromText Constructs a MULTIPOLYGON value
MPolyFromWKB Constructs a MULTIPOLYGON value using its WKB representation and SRID
MultiLineStringFromText Synonym for MLineFromText
MultiLineStringFromWKB A synonym for MLineFromWKB
MULTIPOINT Constructs a WKB MultiPoint value
MultiPointFromText Synonym for MPointFromText
MultiPointFromWKB Synonym for MPointFromWKB
MULTIPOLYGON Constructs a WKB MultiPolygon
MultiPolygonFromText Synonym for MPolyFromText
MultiPolygonFromWKB Synonym for MPolyFromWKB
MULTILINESTRING Constructs a MultiLineString value
NAME_CONST Returns the given value
NOT LIKE Same as NOT(expr LIKE pat [ESCAPE 'escape_char'])
NOT REGEXP Same as NOT (expr REGEXP pat)
NULLIF Returns NULL if expr1 = expr2
NEXTVAL Generate next value for sequence
NOT BETWEEN Same as NOT (expr BETWEEN min AND max)
NOT IN Same as NOT (expr IN (value,...))
NOW Returns the current date and time
NTILE Returns an integer indicating which group a given row falls into
NumGeometries Synonym for ST_NumGeometries
NumInteriorRings Synonym for NumInteriorRings
NumPoints Synonym for ST_NumPoints
OCT Returns octal value
OCTET_LENGTH Synonym for LENGTH()
OLD_PASSWORD Pre MySQL 4.1 password implementation
ORD Return ASCII or character code
OVERLAPS Indicates whether two elements spatially overlap
PASSWORD Calculates a password string
PERCENT_RANK Window function that returns the relative percent rank of a given row
PERCENTILE_CONT Returns a value which corresponds to the given fraction in the sort order.
PERCENTILE_DISC Returns the first value in the set whose ordered position is the same or more than the specified fraction.
PERIOD_ADD Add months to a period
PERIOD_DIFF Number of months between two periods
PI Returns the value of π (pi)
POINT Constructs a WKB Point
PointFromText Synonym for ST_PointFromText
PointFromWKB Synonym for PointFromWKB
PointN Synonym for PointN
PointOnSurface Synonym for ST_PointOnSurface
POLYGON Constructs a WKB Polygon value from a number of WKB LineString arguments
PolyFromText Synonym for ST_PolyFromText
PolyFromWKB Synonym for ST_PolyFromWKB
PolygonFromText Synonym for ST_PolyFromText
PolygonFromWKB Synonym for ST_PolyFromWKB
POSITION Returns the position of a substring in a string
POW Returns X raised to the power of Y
POWER Synonym for POW()
QUARTER Returns year quarter from 1 to 4
QUOTE Returns quoted, properly escaped string
RADIANS Converts from degrees to radians
RAND Random floating-point value
RANK Rank of a given row with identical values receiving the same result
REGEXP Performs pattern matching
REGEXP_INSTR Position of the first appearance of a regex
REGEXP_REPLACE Replaces all occurrences of a pattern
REGEXP_SUBSTR Returns the matching part of a string
RELEASE_LOCK Releases lock obtained with GET_LOCK()
REPEAT Function Returns a string repeated a number of times
REPLACE Function Replace occurrences of a string
REVERSE Reverses the order of a string
RIGHT Returns the rightmost N characters from a string
RLIKE Synonym for REGEXP()
RPAD Returns the string right-padded with another string to a given length
ROUND Rounds a number
ROW_COUNT Number of rows affected by previous statement
ROW_NUMBER Row number of a given row with identical values receiving a different result
RTRIM Returns the string with trailing space characters removed
SCHEMA Synonym for DATABASE()
SECOND Returns the second of a time
SEC_TO_TIME Converts a second to a time
SETVAL Set the next value to be returned by a sequence
SESSION_USER Synonym for USER()
SHA Synonym for SHA1()
SHA1 Calculates an SHA-1 checksum
SHA2 Calculates an SHA-2 checksum
SIGN Returns 1, 0 or -1
SIN Returns the sine
SLEEP Pauses for the given number of seconds
SOUNDEX Returns a string based on how the string sounds
SOUNDS LIKE SOUNDEX(expr1) = SOUNDEX(expr2)
SPACE Returns a string of space characters
SPIDER_BG_DIRECT_SQL Background SQL execution
SPIDER_COPY_TABLES Copy table data
SPIDER_DIRECT_SQL Execute SQL on the remote server
SPIDER_FLUSH_TABLE_MON_CACHE Refreshing Spider monitoring server information
SQRT Square root
SRID Synonym for ST_SRID
ST_AREA Area of a Polygon
ST_AsBinary Converts a value to its WKB representation
ST_AsText Converts a value to its WKT-Definition
ST_AsWKB Synonym for ST_AsBinary
ST_ASWKT Synonym for ST_ASTEXT()
ST_BOUNDARY Returns a geometry that is the closure of a combinatorial boundary
ST_BUFFER A new geometry with a buffer added to the original geometry
ST_CENTROID The mathematical centroid (geometric center) for a MultiPolygon
ST_CONTAINS Whether one geometry is contained by another
ST_CONVEXHULL The minimum convex geometry enclosing all geometries within the set
ST_CROSSES Whether two geometries spatially cross
ST_DIFFERENCE Point set difference
ST_DIMENSION Inherent dimension of a geometry value
ST_DISJOINT Whether one geometry is spatially disjoint from another
ST_DISTANCE The distance between two geometries
ST_ENDPOINT Returns the endpoint of a LineString
ST_ENVELOPE Returns the Minimum Bounding Rectangle for a geometry value
ST_EQUALS Whether two geometries are spatoially equal
ST_ExteriorRing Returns the exterior ring of a Polygon as a LineString
ST_GeomCollFromText Constructs a GEOMETRYCOLLECTION value
ST_GeomCollFromWKB Constructs a GEOMETRYCOLLECTION value from a WKB
ST_GeometryCollectionFromText Synonym for ST_GeomCollFromText
ST_GeometryCollectionFromWKB Synonym for ST_GeomCollFromWKB
ST_GeometryFromText Synonym for ST_GeomFromText
ST_GeometryFromWKB Synonym for ST_GeomFromWKB
ST_GEOMETRYN Returns the N-th geometry in a GeometryCollection
ST_GEOMETRYTYPE Returns name of the geometry type of which a given geometry instance is a member
ST_GeomFromText Constructs a geometry value using its WKT and SRID
ST_GeomFromWKB Constructs a geometry value using its WKB representation and SRID
ST_InteriorRingN Returns the N-th interior ring for a Polygon
ST_INTERSECTION The intersection, or shared portion, of two geometries
ST_INTERSECTS Whether two geometries spatially intersect
ST_ISCLOSED Returns true if a given LINESTRING's start and end points are the same
ST_ISEMPTY Indicated validity of geometry value
ST_IsRing Returns true if a given LINESTRING is both ST_IsClosed and ST_IsSimple
ST_IsSimple Returns true if the given Geometry has no anomalous geometric points
ST_LENGTH Length of a LineString value
ST_LineFromText Creates a linestring value
ST_LineFromWKB Constructs a LINESTRING using its WKB and SRID
ST_LineStringFromText Synonym for ST_LineFromText
ST_LineStringFromWKB Synonym for ST_LineFromWKB
ST_NUMGEOMETRIES Number of geometries in a GeometryCollection
ST_NumInteriorRings Number of interior rings in a Polygon
ST_NUMPOINTS Returns the number of Point objects in a LineString
ST_OVERLAPS Whether two geometries overlap
ST_PointFromText Constructs a POINT value
ST_PointFromWKB Constructs POINT using its WKB and SRID
ST_POINTN Returns the N-th Point in the LineString
ST_POINTONSURFACE Returns a POINT guaranteed to intersect a surface
ST_PolyFromText Constructs a POLYGON value
ST_PolyFromWKB Constructs POLYGON value using its WKB representation and SRID
ST_PolygonFromText Synonym for ST_PolyFromText
ST_PolygonFromWKB Synonym for ST_PolyFromWKB
ST_RELATE Returns true if two geometries are related
ST_SRID Returns a Spatial Reference System ID
ST_STARTPOINT Returns the start point of a LineString
ST_SYMDIFFERENCE Portions of two geometries that don't intersect
ST_TOUCHES Whether one geometry g1 spatially touches another
ST_UNION Union of two geometries
ST_WITHIN Whether one geometry is within another
ST_X X-coordinate value for a point
ST_Y Y-coordinate for a point
STARTPOINT Synonym for ST_StartPoint
STD Population standard deviation
STDDEV Population standard deviation
STDDEV_POP Returns the population standard deviation
STDDEV_SAMP Standard deviation
STR_TO_DATE Converts a string to date
STRCMP Compares two strings in sort order
SUBDATE Subtract a date unit or number of days
SUBSTR Returns a substring from string starting at a given position
SUBSTRING Returns a substring from string starting at a given position
SUBSTRING_INDEX Returns the substring from string before count occurrences of a delimiter
SUBTIME Subtracts a time from a date/time
SUM Sum total
SYSDATE Returns the current date and time
SYSTEM_USER Synonym for USER()
TAN Returns the tangent
TIME function Extracts the time
TIMEDIFF Returns the difference between two date/times
TIMESTAMP FUNCTION Return the datetime, or add a time to a date/time
TIMESTAMPADD Add interval to a date or datetime
TIMESTAMPDIFF Difference between two datetimes
TIME_FORMAT Formats the time value according to the format string
TIME_TO_SEC Returns the time argument, converted to seconds
TO_BASE64 Converts a string to its base-64 encoded form
TO_DAYS Number of days since year 0
TO_SECONDS Number of seconds since year 0
TOUCHES Whether two geometries spatially touch
TRIM Returns a string with all given prefixes or suffixes removed
TRUNCATE Truncates X to D decimal places
UCASE Synonym for UPPER]]()
UNHEX Interprets pairs of hex digits as a number and converts to the character represented by the number
UNCOMPRESS Uncompresses string compressed with COMPRESS()
UNCOMPRESSED_LENGTH Returns length of a string before being compressed with COMPRESS()
UNIX_TIMESTAMP Returns a Unix timestamp
UPDATEXML Replace XML
UPPER Changes string to uppercase
USER Current user/host
UTC_DATE Returns the current UTC date
UTC_TIME Returns the current UTC time
UTC_TIMESTAMP Returns the current UTC date and time
UUID Returns a Universal Unique Identifier
UUID_SHORT Return short universal identifier
VALUES or VALUE Refer to columns in INSERT ... ON DUPLICATE KEY UPDATE
VAR_POP Population standard variance
VAR_SAMP Returns the sample variance
VARIANCE Population standard variance
VERSION MariaDB server version
WEEK Returns the week number
WEEKDAY Returns the weekday index
WEEKOFYEAR Returns the calendar week of the date as a number in the range from 1 to 53
WEIGHT_STRING Weight of the input string
WITHIN Indicate whether a geographic element is spacially within another
X Synonym for ST_X
Y Synonym for ST_Y
YEAR Returns the year for the given date
YEARWEEK Returns year and week for a date
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/function-and-operator-reference/