Comprehensive reference of all SQL functions and operators. This index lists built-in functions for string manipulation, math, date/time, and more.
Addition operator
Division operator
Multiplication operator
This page is licensed: CC BY-SA / Gnu FDL
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
Logical XOR
|
Assignment operator
Assignment and comparison operator
~
Returns an absolute value
Returns an arc cosine
Add months to a date
Add days or another interval to a date
Adds a time to a time or datetime
Decryption data encrypted with AES_ENCRYPT
Encrypts a string with the AES algorithm
Synonym for ST_AREA
Synonym for ST_AsBinary
Numeric ASCII value of leftmost character
Returns the arc sine
Synonym for ST_AsText
Synonym for ST_AsBinary
Synonym for ST_AsText
Returns the arc tangent
Returns the arc tangent of two variables
Returns the average value
Executes an expression repeatedly
True if expression between two values
Returns binary value
Casts to a binary string
Returns a string representation of the corresponding GTID position
Bitwise AND
Returns the number of set bits
Returns the length of a string in bits
Bitwise OR
Bitwise XOR
Synonym for ST_BOUNDARY
Synonym for ST_BUFFER
Returns the result where value=compare_value or for the first condition that is true
Casts a value of one type to another type
Synonym for CEILING()
Returns the smallest integer not less than X
Synonym for ST_CENTROID
Returns string based on the integer values for the individual characters
Synonym for CHAR_LENGTH()
Length of the string in characters
Returns the character set
Returns a string consisting of the character given by the code values of the integer
Returns the first non-NULL parameter
Returns the collation coercibility value
Collation of the string argument
Adds or updates dynamic columns
Checks if a dynamic column blob is valid
Returns a dynamic columns blob
Deletes a dynamic column
Checks is a column exists
Gets a dynamic column value by name
Returns a JSON representation of dynamic column blob data
Returns comma-separated list
Returns a binary, compressed string
Returns concatenated string
Concatenate with separator
Connection thread ID
Whether one geometry contains another
Convert a value from one type to another type
Converts numbers between different number bases
Converts a datetime from on time zone to another
Synonym for ST_CONVEXHULL
Returns the cosine
Returns the cotangent
Returns count of non-null values
Returns count of number of different non-NULL values
Computes a cyclic redundancy check value
Computes a cyclic redundancy check value
Whether two geometries spatially cross
Window function that returns the cumulative distribution of a given row
Returns the current date
Synonym for CURDATE()
Current role name
Synonym for CURTIME()
Synonym for NOW()
Username/host that authenicated the current client
Returns the current time
Current default database
Extracts the date portion of a datetime
Difference in days between two date/time values
Date arithmetic - addition
Formats the date value according to the format string
Date arithmetic - subtraction
Synonym for DAYOFMONTH()
Return the name of the weekday
Returns the day of the month
Returns the day of the week index
Returns the day of the year
Decrypts a string encoded with ENCODE()
Returns comma separated numerics corresponding to a probability distribution represented by a histogram
Returns column default
Converts from radians to degrees
Rank of a given row with identical values receiving the same result, no skipping
Decrypts a string encrypted with DES_ENCRYPT()
Encrypts a string using the Triple-DES algorithm
Synonym for ST_DIMENSION
Whether the two elements do not intersect
Integer division
Returns the N'th element from a set of strings
Encrypts a string
Encrypts a string with Unix crypt()
Synonym for ST_ENDPOINT
Synonym for ST_ENVELOPE
Indicates whether two geometries are spatially equal
e raised to the power of the argument
Returns an on string for every bit set, an off string for every bit not set
Synonym for ST_ExteriorRing
Extracts a portion of the date
Returns the text of the first text node matched by the XPath expression
Returns the index position of a string in a list
Returns the position of a string in a set of strings
Largest integer value not greater than the argument
Formats a number
Given a byte count, returns a string consisting of a value and the units in a human-readable format.
Given a time in picoseconds, returns a human-readable time value and unit indicator
Number of (potentially) returned rows
Given a base-64 encoded string, returns the decoded result as a binary string
Returns a date given a day
Returns a datetime from a Unix timestamp
Synonym for ST_GeomCollFromText
Synonym for ST_GeomCollFromWKB
Constructs a WKB GeometryCollection
Synonym for ST_GeomCollFromText
Synonym for ST_GeomCollFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Synonym for ST_GeometryN
Synonym for ST_GeometryType
Returns a format string
Obtain LOCK
Length of a LineString value
Returns the largest argument
Returns string with concatenated values from a group
Returns hexadecimal value
Returns the hour
If expr1 is TRUE, returns expr2; otherwise returns expr3
Check whether an expression is NULL
True if expression equals any of the values in the list
Index of the argument that is less than the first argument
Given an IPv6 or IPv4 network address, returns a VARBINARY numeric value
Given an IPv6 or IPv4 network address, returns the address as a nonbinary string
Returns numeric value of IPv4 address
Returns dotted-quad representation of IPv4 address
Replaces a part of a string with another string
Returns the position of a string withing a string
Synonym for ST_InteriorRingN
Indicates whether two geometries spatially intersect
Tests whether a boolean is TRUE, FALSE, or UNKNOWN
Synonym for ST_IsClosed
Synonym for ST_IsEmpty
Checks whether lock is free to use
Whether or not an expression is a valid IPv4 address
Whether or not an IPv6 address is IPv4-compatible
Whether an IPv6 address is a valid IPv4-mapped address
Whether or not an expression is a valid IPv6 address
Tests whether a boolean value is not TRUE, FALSE, or UNKNOWN
Tests whether a value is not NULL
Tests whether a value is NULL
Checks if an expression is NULL
Synonym for ST_IsRing
Synonym for ST_IsSimple
Check if lock is in use
Returns a JSON array containing the listed values
Returns a JSON array containing an element for each value in a given set of JSON or SQL values.
Appends values to the end of the given arrays within a JSON document
Inserts a value into a JSON document
Removes all unnecessary spaces so the json document is as short as possible
Whether a value is found in a given JSON document or at a specified path within the document
Indicates whether the given JSON document contains data at the specified path or paths
Maximum depth of a JSON document
Represents JSON in the most understandable way emphasizing nested structures
Check for equality between JSON objects.
Determines whether a specified JSON value exists in the given data
Extracts data from a JSON document.
Inserts data into a JSON document
Extracts key/value pairs from a JSON object
Returns keys from top-level value of a JSON object or top-level keys from the path
Returns the length of a JSON document, or the length of a value within the document
Adds spaces to a JSON document to make it look more readable
Merges the given JSON documents
RFC 7396-compliant merge of the given JSON documents
Synonym for JSON_MERGE_PATCH.
Recursively sorts keys and removes spaces, allowing comparison of json documents for equality
Returns a JSON object containing the given key/value pairs
Returns a JSON object containing key-value pairs
Compares two json documents for overlaps
Alias for json_detailed
, , , , MariaDB 10.6.12
Given a JSON document, returns an object or array specified by the path
Quotes a string as a JSON value
Removes data from a JSON document
Replaces existing values in a JSON document
Validates a JSON schema
Returns the path to the given string within a JSON document
Updates or inserts data into a JSON document
Returns a representation of a JSON document as a relational table
Returns the type of a JSON value
Unquotes a JSON value, returning a string
Whether a value is a valid JSON document or not
Given a JSON document, returns the specified scalar
Key derivation function
Returns the last day of the month
Last inserted autoinc value
Returns the last value in a list
Get last value generated from a sequence
Synonym for [LOWER()
Returns the smallest argument
Returns the leftmost characters from a string
Length of the string in bytes
Whether expression matches a pattern
Synonym for ST_LineFromText
Synonym for ST_LineFromWKB
Constructs a WKB LineString value from a number of WKB Point arguments
Synonym for ST_LineFromText
Synonym for ST_LineFromWKB
Returns natural logarithm
Returns file contents as a string
Synonym for NOW()
Synonym for NOW()
Returns the position of a substring in a string
Returns the natural logarithm
Returns the base-10 logarithm
Returns the base-2 logarithm
Returns a string with all characters changed to lowercase
Returns the string left-padded with another string to a given length
Returns the string with leading space characters removed
Make a set of strings that matches a bitmask
Returns a date given a year and day
Returns a time
Wait until slave reaches the GTID position
Blocks until the slave has applied all specified updates
Perform a fulltext search on a fulltext index
Returns the maximum value
Whether one Minimum Bounding Rectangle contains another.
Whether one Minimum Bounding Rectangle is covered by another.
Whether the Minimum Bounding Rectangles of two geometries are disjoint
Whether the Minimum Bounding Rectangles of two geometries are the same.
Synonym for MBREqual.
Indicates whether the Minimum Bounding Rectangles of the two geometries intersect
Whether the Minimum Bounding Rectangles of two geometries overlap
Whether the Minimum Bounding Rectangles of two geometries touch.
Indicates whether one Minimum Bounding Rectangle is within another
MD5 checksum
Window function that returns the median value of a range of values
Returns microseconds from a date or datetime
Synonym for SUBSTRING(str,pos,len)
Returns the minimum value
Returns a minute from 0 to 59
Constructs MULTILINESTRING using its WKT representation and SRID
Constructs a MULTILINESTRING
Modulo operation. Remainder of N divided by M
Returns a month from 1 to 12
Returns the full name of the month
Constructs a MULTIPOINT value using its WKT and SRID
Constructs a MULTIPOINT value using its WKB representation and SRID
Constructs a MULTIPOLYGON value
Constructs a MULTIPOLYGON value using its WKB representation and SRID
Constructs a MultiLineString value
Synonym for MLineFromText
A synonym for MLineFromWKB
Constructs a WKB MultiPoint value
Synonym for MPointFromText
Synonym for MPointFromWKB
Constructs a WKB MultiPolygon
Synonym for MPolyFromText
Synonym for MPolyFromWKB
Returns the given value
Sorting that is more similar to natural human sorting
Same as NOT(expr LIKE pat [ESCAPE 'escape_char'])
Same as NOT (expr REGEXP pat)
Returns NULL if expr1 = expr2
Generate next value for sequence
Same as NOT (expr BETWEEN min AND max)
Same as NOT (expr IN (value,...))
Returns the current date and time
Returns an integer indicating which group a given row falls into
Synonym for ST_NumGeometries
Synonym for NumInteriorRings
Synonym for ST_NumPoints
Returns octal value
Synonym for LENGTH()
Pre MySQL 4.1 password implementation
Return ASCII or character code
Indicates whether two elements spatially overlap
Calculates a password string
Window function that returns the relative percent rank of a given row
Returns a value which corresponds to the given fraction in the sort order.
Returns the first value in the set whose ordered position is the same or more than the specified fraction.
Add months to a period
Number of months between two periods
Returns the value of π (pi)
Constructs a WKB Point
Synonym for ST_PointFromText
Synonym for PointFromWKB
Synonym for PointN
Synonym for ST_PointOnSurface
Constructs a WKB Polygon value from a number of WKB LineString arguments
Synonym for ST_PolyFromText
Synonym for ST_PolyFromWKB
Synonym for ST_PolyFromText
Synonym for ST_PolyFromWKB
Returns the position of a substring in a string
Returns X raised to the power of Y
Synonym for POW()
Returns year quarter from 1 to 4
Returns quoted, properly escaped string
Converts from degrees to radians
Random floating-point value
Rank of a given row with identical values receiving the same result
Performs pattern matching
Position of the first appearance of a regex
Replaces all occurrences of a pattern
Returns the matching part of a string
Releases lock obtained with GET_LOCK()
Returns a string repeated a number of times
Replace occurrences of a string
Reverses the order of a string
Returns the rightmost N characters from a string
Synonym for REGEXP()
Returns the string right-padded with another string to a given length
Rounds a number
Number of rows affected by previous statement
Row number of a given row with identical values receiving a different result
Returns the string with trailing space characters removed
Synonym for DATABASE()
Returns the second of a time
Converts a second to a time
Set the next value to be returned by a sequence
Synonym for USER()
Synonym for SHA1()
Calculates an SHA-1 checksum
Calculates an SHA-2 checksum
Returns 1, 0 or -1
Returns the sine
Pauses for the given number of seconds
Returns a string based on how the string sounds
SOUNDEX(expr1) = SOUNDEX(expr2)
Returns a string of space characters
Background SQL execution
Copy table data
Execute SQL on the remote server
Refreshing Spider monitoring server information
Square root
Synonym for ST_SRID
Area of a Polygon
Converts a value to its WKB representation
Returns a GeoJSON element from a given geometry.
Converts a value to its WKT-Definition
Synonym for ST_AsBinary
Synonym for ST_ASTEXT()
Returns a geometry that is the closure of a combinatorial boundary
A new geometry with a buffer added to the original geometry
The mathematical centroid (geometric center) for a MultiPolygon
Returns the aggregation of the distinct geometry arguments
Whether one geometry is contained by another
The minimum convex geometry enclosing all geometries within the set
Whether two geometries spatially cross
Point set difference
Inherent dimension of a geometry value
Whether one geometry is spatially disjoint from another
The distance between two geometries
The spherical distance between two geometries
Returns the endpoint of a LineString
Returns the Minimum Bounding Rectangle for a geometry value
Whether two geometries are spatoially equal
Returns the exterior ring of a Polygon as a LineString
Returns a geohash.
Constructs a GEOMETRYCOLLECTION value
Constructs a GEOMETRYCOLLECTION value from a WKB
Synonym for ST_GeomCollFromText
Synonym for ST_GeomCollFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Returns the N-th geometry in a GeometryCollection
Returns name of the geometry type of which a given geometry instance is a member
Returns a geometry object from a GeoJSON input
Constructs a geometry value using its WKT and SRID
Constructs a geometry value using its WKB representation and SRID
Returns the N-th interior ring for a Polygon
The intersection, or shared portion, of two geometries
Whether two geometries spatially intersect
Returns true if a given LINESTRING's start and end points are the same
Indicated validity of geometry value
Returns true if a given LINESTRING is both ST_IsClosed and ST_IsSimple
Returns true if the given Geometry has no anomalous geometric points
Returns 1 if the argument is geometrically valid, 0 if not.
Returns a latitude from the given geohash.
Returns a longitude from the given geohash.
Length of a LineString value
Creates a linestring value
Constructs a LINESTRING using its WKB and SRID
Synonym for ST_LineFromText
Synonym for ST_LineFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Synonym for ST_GeomFromText
Synonym for ST_GeomFromText
Synonym for ST_GeomFromWKB
Synonym for ST_GeomFromWKB
Number of geometries in a GeometryCollection
Number of interior rings in a Polygon
Returns the number of Point objects in a LineString
Whether two geometries overlap
Returns a point from the given geohash.
Constructs a POINT value
Constructs POINT using its WKB and SRID
Returns the N-th Point in the LineString
Returns a POINT guaranteed to intersect a surface
Constructs a POLYGON value
Constructs POLYGON value using its WKB representation and SRID
Synonym for ST_PolyFromText
Synonym for ST_PolyFromWKB
Returns true if two geometries are related
Applies the Ramer–Douglas–Peucker algorithm to provide generalized simplifications.
Returns a Spatial Reference System ID
Returns the start point of a LineString
Portions of two geometries that don't intersect
Whether one geometry g1 spatially touches another
Union of two geometries
Whether a geometry is compliant with the WKB format and SRID syntax, and is geometrically valid.
Whether one geometry is within another
X-coordinate value for a point
Y-coordinate for a point
Synonym for ST_StartPoint
Population standard deviation
Population standard deviation
Returns the population standard deviation
Standard deviation
Converts a string to date
Compares two strings in sort order
Subtract a date unit or number of days
Returns a substring from string starting at a given position
Returns a substring from string starting at a given position
Returns the substring from string before count occurrences of a delimiter
Subtracts a time from a date/time
Sum total
Given a file path, returns the schema (database) name
Given a file path, returns the table name
Returns a string consisting of a value and the units in a human-readable format
Returns a modified path after replacing subpaths matching the values of various system variables with the variable name
Returns a reduced length string
Returns a human-readable time value and unit indicator
Adds a value to a given list
Drops a value from a given list
Whether Performance Schema instrumentation for the given account is enabled
Whether Performance Schema instrumentation for the given consumer is enabled
Whether a given Performance Schema instrument is enabled by default
Returns whether a given Performance Schema instrument is timed by default
Returns whether or not Performance Schema instrumentation for the given connection_id is enabled
Returns the account (username@hostname) associated with the given thread_id
Returns the thread_id associated with the given connection_id
Returns all statements, stages, and events within the Performance Schema for a given thread_id
Returns a JSON object with information about the thread specified by the given thread_id
Quotes a string to produce a result that can be used as an identifier in an SQL statement
Returns a configuration option value from the sys_config table
Returns the MariaDB Server major release version
Returns the MariaDB Server minor release version
Returns the MariaDB Server patch release version
Generates a globally unique identifier
Returns the current date and time
Synonym for USER()
Returns the tangent
Extracts the time
Returns the difference between two date/times
Return the datetime, or add a time to a date/time
Add interval to a date or datetime
Difference between two datetimes
Formats the time value according to the format string
Returns the time argument, converted to seconds
Converts a string to its base-64 encoded form
Converts a date/time type to a char
Number of days since year 0
Converts and expression to NUMERIC
Number of seconds since year 0
Whether two geometries spatially touch
Returns a string with all given prefixes or suffixes removed
Returns a DATETIME truncated according to a given format.
Truncates X to D decimal places
Synonym for UPPER]]()
Interprets pairs of hex digits as a number and converts to the character represented by the number
Uncompresses string compressed with COMPRESS()
Returns length of a string before being compressed with COMPRESS()
Returns a Unix timestamp
Replace XML
Changes string to uppercase
Current user/host
Returns the current UTC date
Returns the current UTC time
Returns the current UTC date and time
Returns a Universal Unique Identifier v1
Returns a Universal Unique Identifier v4
Returns a Universal Unique Identifier v7
Return short universal identifier
Refer to columns in INSERT ... ON DUPLICATE KEY UPDATE
Population standard variance
Returns the sample variance
Population standard variance
Calculates either a Euclidean or Cosine distance between two vectors.
Calculates a Cosine distance between two vectors.
Calculates a Euclidean (L2) distance between two points.
Converts a text representation of the vector to a vector.
Vector
Converts a binary vector into a json array of numbers (floats).
Vector
MariaDB server version
Returns the week number
Returns the weekday index
Returns the calendar week of the date as a number in the range from 1 to 53
Weight of the input string
Indicate whether a geographic element is spacially within another
Returns the Global Transaction ID of the most recent write transaction observed by the client.
Returns the Global Transaction ID of the most recent write transaction performed by the client.
Blocks the client until the transaction specified by the given Global Transaction ID is applied and committed by the node
Synonym for ST_X
Synonym for ST_Y
Returns the year for the given date
Returns year and week for a date