Chapter 12 Functions and Operators
Prev		Next

Chapter 12 Functions and Operators

Table of Contents

Expressions can be used at several points in SQL statements, such as in the ORDER BY or HAVING clauses of SELECT statements, in the WHERE clause of a SELECT, DELETE, or UPDATE statement, or in SET statements. Expressions can be written using literal values, column values, NULL, built-in functions, stored functions, user-defined functions, and operators. This chapter describes the functions and operators that are permitted for writing expressions in MySQL. Instructions for writing stored functions and user-defined functions are given in Section 23.2, “Using Stored Routines (Procedures and Functions)”, and Section 28.4, “Adding New Functions to MySQL”. See Section 9.2.4, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of functions.

An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for a particular function or operator.

Note

By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. However, spaces around function arguments are permitted.

You can tell the MySQL server to accept spaces after function names by starting it with the --sql-mode=IGNORE_SPACE option. (See Section 5.1.10, “Server SQL Modes”.) Individual client programs can request this behavior by using the CLIENT_IGNORE_SPACE option for mysql_real_connect(). In either case, all function names become reserved words.

For the sake of brevity, most examples in this chapter display the output from the mysql program in abbreviated form. Rather than showing examples in this format:

mysql> SELECT MOD(29,9);
+-----------+
| mod(29,9) |
+-----------+
|         2 |
+-----------+
1 rows in set (0.00 sec)

This format is used instead:

mysql> SELECT MOD(29,9);
        -> 2

12.1 Function and Operator Reference

Table 12.1 Functions and Operators

Name	Description
`ABS()`	Return the absolute value
`ACOS()`	Return the arc cosine
`ADDDATE()`	Add time values (intervals) to a date value
`ADDTIME()`	Add time
`AES_DECRYPT()`	Decrypt using AES
`AES_ENCRYPT()`	Encrypt using AES
`AND`, `&&`	Logical AND
`ANY_VALUE()`	Suppress ONLY_FULL_GROUP_BY value rejection
`ASCII()`	Return numeric value of left-most character
`ASIN()`	Return the arc sine
`=`	Assign a value (as part of a `SET` statement, or as part of the `SET` clause in an `UPDATE` statement)
`:=`	Assign a value
`ASYMMETRIC_DECRYPT()`	Decrypt ciphertext using private or public key
`ASYMMETRIC_DERIVE()`	Derive symmetric key from asymmetric keys
`ASYMMETRIC_ENCRYPT()`	Encrypt cleartext using private or public key
`ASYMMETRIC_SIGN()`	Generate signature from digest
`ASYMMETRIC_VERIFY()`	Verify that signature matches digest
`ATAN()`	Return the arc tangent
`ATAN2()`, `ATAN()`	Return the arc tangent of the two arguments
`AVG()`	Return the average value of the argument
`BENCHMARK()`	Repeatedly execute an expression
`BETWEEN ... AND ...`	Check whether a value is within a range of values
`BIN()`	Return a string containing binary representation of a number
`BIN_TO_UUID()`	Convert binary UUID to string
`BINARY`	Cast a string to a binary string
`BIT_AND()`	Return bitwise AND
`BIT_COUNT()`	Return the number of bits that are set
`BIT_LENGTH()`	Return length of argument in bits
`BIT_OR()`	Return bitwise OR
`BIT_XOR()`	Return bitwise XOR
`&`	Bitwise AND
`~`	Bitwise inversion
`\|`	Bitwise OR
`^`	Bitwise XOR
`CAN_ACCESS_COLUMN()`	Internal use only
`CAN_ACCESS_DATABASE()`	Internal use only
`CAN_ACCESS_TABLE()`	Internal use only
`CAN_ACCESS_VIEW()`	Internal use only
`CASE`	Case operator
`CAST()`	Cast a value as a certain type
`CEIL()`	Return the smallest integer value not less than the argument
`CEILING()`	Return the smallest integer value not less than the argument
`CHAR()`	Return the character for each integer passed
`CHAR_LENGTH()`	Return number of characters in argument
`CHARACTER_LENGTH()`	Synonym for CHAR_LENGTH()
`CHARSET()`	Return the character set of the argument
`COALESCE()`	Return the first non-NULL argument
`COERCIBILITY()`	Return the collation coercibility value of the string argument
`COLLATION()`	Return the collation of the string argument
`COMPRESS()`	Return result as a binary string
`CONCAT()`	Return concatenated string
`CONCAT_WS()`	Return concatenate with separator
`CONNECTION_ID()`	Return the connection ID (thread ID) for the connection
`CONV()`	Convert numbers between different number bases
`CONVERT()`	Cast a value as a certain type
`CONVERT_TZ()`	Convert from one time zone to another
`COS()`	Return the cosine
`COT()`	Return the cotangent
`COUNT()`	Return a count of the number of rows returned
`COUNT(DISTINCT)`	Return the count of a number of different values
`CRC32()`	Compute a cyclic redundancy check value
`CREATE_ASYMMETRIC_PRIV_KEY()`	Create private key
`CREATE_ASYMMETRIC_PUB_KEY()`	Create public key
`CREATE_DH_PARAMETERS()`	Generate shared DH secret
`CREATE_DIGEST()`	Generate digest from string
`CUME_DIST()`	Cumulative distribution value
`CURDATE()`	Return the current date
`CURRENT_DATE()`, `CURRENT_DATE`	Synonyms for CURDATE()
`CURRENT_ROLE()`	Returns the current active roles
`CURRENT_TIME()`, `CURRENT_TIME`	Synonyms for CURTIME()
`CURRENT_TIMESTAMP()`, `CURRENT_TIMESTAMP`	Synonyms for NOW()
`CURRENT_USER()`, `CURRENT_USER`	The authenticated user name and host name
`CURTIME()`	Return the current time
`DATABASE()`	Return the default (current) database name
`DATE()`	Extract the date part of a date or datetime expression
`DATE_ADD()`	Add time values (intervals) to a date value
`DATE_FORMAT()`	Format date as specified
`DATE_SUB()`	Subtract a time value (interval) from a date
`DATEDIFF()`	Subtract two dates
`DAY()`	Synonym for DAYOFMONTH()
`DAYNAME()`	Return the name of the weekday
`DAYOFMONTH()`	Return the day of the month (0-31)
`DAYOFWEEK()`	Return the weekday index of the argument
`DAYOFYEAR()`	Return the day of the year (1-366)
`DECODE()`	Decodes a string encrypted using ENCODE()
`DEFAULT()`	Return the default value for a table column
`DEGREES()`	Convert radians to degrees
`DENSE_RANK()`	Rank of current row within its partition, without gaps
`DES_DECRYPT()`	Decrypt a string
`DES_ENCRYPT()`	Encrypt a string
`DIV`	Integer division
`/`	Division operator
`ELT()`	Return string at index number
`ENCODE()`	Encode a string
`ENCRYPT()`	Encrypt a string
`=`	Equal operator
`<=>`	NULL-safe equal to operator
`EXP()`	Raise to the power of
`EXPORT_SET()`	Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
`EXTRACT()`	Extract part of a date
`ExtractValue()`	Extracts a value from an XML string using XPath notation
`FIELD()`	Return the index (position) of the first argument in the subsequent arguments
`FIND_IN_SET()`	Return the index position of the first argument within the second argument
`FIRST_VALUE()`	Value of argument from first row of window frame
`FLOOR()`	Return the largest integer value not greater than the argument
`FORMAT()`	Return a number formatted to specified number of decimal places
`FOUND_ROWS()`	For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause
`FROM_BASE64()`	Decode base64 encoded string and return result
`FROM_DAYS()`	Convert a day number to a date
`FROM_UNIXTIME()`	Format Unix timestamp as a date
`GeomCollection()`	Construct geometry collection from geometries
`GeometryCollection()`	Construct geometry collection from geometries
`GET_DD_COLUMN_PRIVILEGES()`	Internal use only
`GET_DD_CREATE_OPTIONS()`	Internal use only
`GET_DD_INDEX_SUB_PART_LENGTH()`	Internal use only
`GET_FORMAT()`	Return a date format string
`GET_LOCK()`	Get a named lock
`>`	Greater than operator
`>=`	Greater than or equal operator
`GREATEST()`	Return the largest argument
`GROUP_CONCAT()`	Return a concatenated string
`GROUPING()`	Distinguish super-aggregate ROLLUP rows from regular rows
`GTID_SUBSET()`	Return true if all GTIDs in subset are also in set; otherwise false.
`GTID_SUBTRACT()`	Return all GTIDs in set that are not in subset.
`HEX()`	Return a hexadecimal representation of a decimal or string value
`HOUR()`	Extract the hour
`ICU_VERSION()`	ICU library version
`IF()`	If/else construct
`IFNULL()`	Null if/else construct
`IN()`	Check whether a value is within a set of values
`INET_ATON()`	Return the numeric value of an IP address
`INET_NTOA()`	Return the IP address from a numeric value
`INET6_ATON()`	Return the numeric value of an IPv6 address
`INET6_NTOA()`	Return the IPv6 address from a numeric value
`INSERT()`	Insert a substring at the specified position up to the specified number of characters
`INSTR()`	Return the index of the first occurrence of substring
`INTERNAL_AUTO_INCREMENT()`	Internal use only
`INTERNAL_AVG_ROW_LENGTH()`	Internal use only
`INTERNAL_CHECK_TIME()`	Internal use only
`INTERNAL_CHECKSUM()`	Internal use only
`INTERNAL_DATA_FREE()`	Internal use only
`INTERNAL_DATA_LENGTH()`	Internal use only
`INTERNAL_DD_CHAR_LENGTH()`	Internal use only
`INTERNAL_GET_COMMENT_OR_ERROR()`	Internal use only
`INTERNAL_GET_VIEW_WARNING_OR_ERROR()`	Internal use only
`INTERNAL_INDEX_COLUMN_CARDINALITY()`	Internal use only
`INTERNAL_INDEX_LENGTH()`	Internal use only
`INTERNAL_KEYS_DISABLED()`	Internal use only
`INTERNAL_MAX_DATA_LENGTH()`	Internal use only
`INTERNAL_TABLE_ROWS()`	Internal use only
`INTERNAL_UPDATE_TIME()`	Internal use only
`INTERVAL()`	Return the index of the argument that is less than the first argument
`IS`	Test a value against a boolean
`IS_FREE_LOCK()`	Whether the named lock is free
`IS_IPV4()`	Whether argument is an IPv4 address
`IS_IPV4_COMPAT()`	Whether argument is an IPv4-compatible address
`IS_IPV4_MAPPED()`	Whether argument is an IPv4-mapped address
`IS_IPV6()`	Whether argument is an IPv6 address
`IS NOT`	Test a value against a boolean
`IS NOT NULL`	NOT NULL value test
`IS NULL`	NULL value test
`IS_USED_LOCK()`	Whether the named lock is in use; return connection identifier if true
`IS_UUID()`	Whether argument is a valid UUID
`ISNULL()`	Test whether the argument is NULL
`JSON_ARRAY()`	Create JSON array
`JSON_ARRAY_APPEND()`	Append data to JSON document
`JSON_ARRAY_INSERT()`	Insert into JSON array
`JSON_ARRAYAGG()`	Return result set as a single JSON array
`->`	Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
`JSON_CONTAINS()`	Whether JSON document contains specific object at path
`JSON_CONTAINS_PATH()`	Whether JSON document contains any data at path
`JSON_DEPTH()`	Maximum depth of JSON document
`JSON_EXTRACT()`	Return data from JSON document
`->>`	Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
`JSON_INSERT()`	Insert data into JSON document
`JSON_KEYS()`	Array of keys from JSON document
`JSON_LENGTH()`	Number of elements in JSON document
`JSON_MERGE()` (deprecated 8.0.3)	Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()
`JSON_MERGE_PATCH()`	Merge JSON documents, replacing values of duplicate keys
`JSON_MERGE_PRESERVE()`	Merge JSON documents, preserving duplicate keys
`JSON_OBJECT()`	Create JSON object
`JSON_OBJECTAGG()`	Return result set as a single JSON object
`JSON_PRETTY()`	Prints a JSON document in human-readable format, with each array element or object member printed on a new line, indented two spaces with respect to its parent.
`JSON_QUOTE()`	Quote JSON document
`JSON_REMOVE()`	Remove data from JSON document
`JSON_REPLACE()`	Replace values in JSON document
`JSON_SEARCH()`	Path to value within JSON document
`JSON_SET()`	Insert data into JSON document
`JSON_STORAGE_FREE()`	Freed space within binary representation of a JSON column value following a partial update
`JSON_STORAGE_SIZE()`	Space used for storage of binary representation of a JSON document; for a JSON column, the space used when the document was inserted, prior to any partial updates
`JSON_TABLE()`	Returns data from a JSON expression as a relational table
`JSON_TYPE()`	Type of JSON value
`JSON_UNQUOTE()`	Unquote JSON value
`JSON_VALID()`	Whether JSON value is valid
`LAG()`	Value of argument from row lagging current row within partition
`LAST_DAY`	Return the last day of the month for the argument
`LAST_INSERT_ID()`	Value of the AUTOINCREMENT column for the last INSERT
`LAST_VALUE()`	Value of argument from last row of window frame
`LCASE()`	Synonym for LOWER()
`LEAD()`	Value of argument from row leading current row within partition
`LEAST()`	Return the smallest argument
`LEFT()`	Return the leftmost number of characters as specified
`<<`	Left shift
`LENGTH()`	Return the length of a string in bytes
`<`	Less than operator
`<=`	Less than or equal operator
`LIKE`	Simple pattern matching
`LineString()`	Construct LineString from Point values
`LN()`	Return the natural logarithm of the argument
`LOAD_FILE()`	Load the named file
`LOCALTIME()`, `LOCALTIME`	Synonym for NOW()
`LOCALTIMESTAMP`, `LOCALTIMESTAMP()`	Synonym for NOW()
`LOCATE()`	Return the position of the first occurrence of substring
`LOG()`	Return the natural logarithm of the first argument
`LOG10()`	Return the base-10 logarithm of the argument
`LOG2()`	Return the base-2 logarithm of the argument
`LOWER()`	Return the argument in lowercase
`LPAD()`	Return the string argument, left-padded with the specified string
`LTRIM()`	Remove leading spaces
`MAKE_SET()`	Return a set of comma-separated strings that have the corresponding bit in bits set
`MAKEDATE()`	Create a date from the year and day of year
`MAKETIME()`	Create time from hour, minute, second
`MASTER_POS_WAIT()`	Block until the slave has read and applied all updates up to the specified position
`MATCH`	Perform full-text search
`MAX()`	Return the maximum value
`MBRContains()`	Whether MBR of one geometry contains MBR of another
`MBRCoveredBy()`	Whether one MBR is covered by another
`MBRCovers()`	Whether one MBR covers another
`MBRDisjoint()`	Whether MBRs of two geometries are disjoint
`MBREquals()`	Whether MBRs of two geometries are equal
`MBRIntersects()`	Whether MBRs of two geometries intersect
`MBROverlaps()`	Whether MBRs of two geometries overlap
`MBRTouches()`	Whether MBRs of two geometries touch
`MBRWithin()`	Whether MBR of one geometry is within MBR of another
`MD5()`	Calculate MD5 checksum
`MICROSECOND()`	Return the microseconds from argument
`MID()`	Return a substring starting from the specified position
`MIN()`	Return the minimum value
`-`	Minus operator
`MINUTE()`	Return the minute from the argument
`MOD()`	Return the remainder
`%`, `MOD`	Modulo operator
`MONTH()`	Return the month from the date passed
`MONTHNAME()`	Return the name of the month
`MultiLineString()`	Contruct MultiLineString from LineString values
`MultiPoint()`	Construct MultiPoint from Point values
`MultiPolygon()`	Construct MultiPolygon from Polygon values
`NAME_CONST()`	Causes the column to have the given name
`NOT`, `!`	Negates value
`NOT BETWEEN ... AND ...`	Check whether a value is not within a range of values
`!=`, `<>`	Not equal operator
`NOT IN()`	Check whether a value is not within a set of values
`NOT LIKE`	Negation of simple pattern matching
`NOT REGEXP`	Negation of REGEXP
`NOW()`	Return the current date and time
`NTH_VALUE()`	Value of argument from N-th row of window frame
`NTILE()`	Bucket number of current row within its partition.
`NULLIF()`	Return NULL if expr1 = expr2
`OCT()`	Return a string containing octal representation of a number
`OCTET_LENGTH()`	Synonym for LENGTH()
`\|\|`, `OR`	Logical OR
`ORD()`	Return character code for leftmost character of the argument
`PASSWORD()`	Calculate and return a password string
`PERCENT_RANK()`	Percentage rank value
`PERIOD_ADD()`	Add a period to a year-month
`PERIOD_DIFF()`	Return the number of months between periods
`PI()`	Return the value of pi
`+`	Addition operator
`Point()`	Construct Point from coordinates
`Polygon()`	Construct Polygon from LineString arguments
`POSITION()`	Synonym for LOCATE()
`POW()`	Return the argument raised to the specified power
`POWER()`	Return the argument raised to the specified power
`QUARTER()`	Return the quarter from a date argument
`QUOTE()`	Escape the argument for use in an SQL statement
`RADIANS()`	Return argument converted to radians
`RAND()`	Return a random floating-point value
`RANDOM_BYTES()`	Return a random byte vector
`RANK()`	Rank of current row within its partition, with gaps
`REGEXP`	Whether string matches regular expression
`REGEXP_INSTR()`	Starting index of substring matching regular expression
`REGEXP_LIKE()`	Whether string matches regular expression
`REGEXP_REPLACE()`	Replace substrings matching regular expression
`REGEXP_SUBSTR()`	Return substring matching regular expression
`RELEASE_ALL_LOCKS()`	Releases all current named locks
`RELEASE_LOCK()`	Releases the named lock
`REPEAT()`	Repeat a string the specified number of times
`REPLACE()`	Replace occurrences of a specified string
`REVERSE()`	Reverse the characters in a string
`RIGHT()`	Return the specified rightmost number of characters
`>>`	Right shift
`RLIKE`	Whether string matches regular expression
`ROLES_GRAPHML()`	Returns a GraphML document representing memory role subgraphs
`ROUND()`	Round the argument
`ROW_COUNT()`	The number of rows updated
`ROW_NUMBER()`	Number of current row within its partition
`RPAD()`	Append string the specified number of times
`RTRIM()`	Remove trailing spaces
`SCHEMA()`	Synonym for DATABASE()
`SEC_TO_TIME()`	Converts seconds to 'HH:MM:SS' format
`SECOND()`	Return the second (0-59)
`SESSION_USER()`	Synonym for USER()
`SHA1()`, `SHA()`	Calculate an SHA-1 160-bit checksum
`SHA2()`	Calculate an SHA-2 checksum
`SIGN()`	Return the sign of the argument
`SIN()`	Return the sine of the argument
`SLEEP()`	Sleep for a number of seconds
`SOUNDEX()`	Return a soundex string
`SOUNDS LIKE`	Compare sounds
`SPACE()`	Return a string of the specified number of spaces
`SQRT()`	Return the square root of the argument
`ST_Area()`	Return Polygon or MultiPolygon area
`ST_AsBinary()`, `ST_AsWKB()`	Convert from internal geometry format to WKB
`ST_AsGeoJSON()`	Generate GeoJSON object from geometry
`ST_AsText()`, `ST_AsWKT()`	Convert from internal geometry format to WKT
`ST_Buffer()`	Return geometry of points within given distance from geometry
`ST_Buffer_Strategy()`	Produce strategy option for ST_Buffer()
`ST_Centroid()`	Return centroid as a point
`ST_Contains()`	Whether one geometry contains another
`ST_ConvexHull()`	Return convex hull of geometry
`ST_Crosses()`	Whether one geometry crosses another
`ST_Difference()`	Return point set difference of two geometries
`ST_Dimension()`	Dimension of geometry
`ST_Disjoint()`	Whether one geometry is disjoint from another
`ST_Distance()`	The distance of one geometry from another
`ST_Distance_Sphere()`	Minimum distance on earth between two geometries
`ST_EndPoint()`	End Point of LineString
`ST_Envelope()`	Return MBR of geometry
`ST_Equals()`	Whether one geometry is equal to another
`ST_ExteriorRing()`	Return exterior ring of Polygon
`ST_GeoHash()`	Produce a geohash value
`ST_GeomCollFromText()`, `ST_GeometryCollectionFromText()`, `ST_GeomCollFromTxt()`	Return geometry collection from WKT
`ST_GeomCollFromWKB()`, `ST_GeometryCollectionFromWKB()`	Return geometry collection from WKB
`ST_GeometryN()`	Return N-th geometry from geometry collection
`ST_GeometryType()`	Return name of geometry type
`ST_GeomFromGeoJSON()`	Generate geometry from GeoJSON object
`ST_GeomFromText()`, `ST_GeometryFromText()`	Return geometry from WKT
`ST_GeomFromWKB()`, `ST_GeometryFromWKB()`	Return geometry from WKB
`ST_InteriorRingN()`	Return N-th interior ring of Polygon
`ST_Intersection()`	Return point set intersection of two geometries
`ST_Intersects()`	Whether one geometry intersects another
`ST_IsClosed()`	Whether a geometry is closed and simple
`ST_IsEmpty()`	Placeholder function
`ST_IsSimple()`	Whether a geometry is simple
`ST_IsValid()`	Whether a geometry is valid
`ST_LatFromGeoHash()`	Return latitude from geohash value
`ST_Latitude()`	Return latitude of Point
`ST_Length()`	Return length of LineString
`ST_LineFromText()`, `ST_LineStringFromText()`	Construct LineString from WKT
`ST_LineFromWKB()`, `ST_LineStringFromWKB()`	Construct LineString from WKB
`ST_LongFromGeoHash()`	Return longitude from geohash value
`ST_Longitude()`	Return longitude of Point
`ST_MakeEnvelope()`	Rectangle around two points
`ST_MLineFromText()`, `ST_MultiLineStringFromText()`	Construct MultiLineString from WKT
`ST_MLineFromWKB()`, `ST_MultiLineStringFromWKB()`	Construct MultiLineString from WKB
`ST_MPointFromText()`, `ST_MultiPointFromText()`	Construct MultiPoint from WKT
`ST_MPointFromWKB()`, `ST_MultiPointFromWKB()`	Construct MultiPoint from WKB
`ST_MPolyFromText()`, `ST_MultiPolygonFromText()`	Construct MultiPolygon from WKT
`ST_MPolyFromWKB()`, `ST_MultiPolygonFromWKB()`	Construct MultiPolygon from WKB
`ST_NumGeometries()`	Return number of geometries in geometry collection
`ST_NumInteriorRing()`, `ST_NumInteriorRings()`	Return number of interior rings in Polygon
`ST_NumPoints()`	Return number of points in LineString
`ST_Overlaps()`	Whether one geometry overlaps another
`ST_PointFromGeoHash()`	Convert geohash value to POINT value
`ST_PointFromText()`	Construct Point from WKT
`ST_PointFromWKB()`	Construct Point from WKB
`ST_PointN()`	Return N-th point from LineString
`ST_PolyFromText()`, `ST_PolygonFromText()`	Construct Polygon from WKT
`ST_PolyFromWKB()`, `ST_PolygonFromWKB()`	Construct Polygon from WKB
`ST_Simplify()`	Return simplified geometry
`ST_SRID()`	Return spatial reference system ID for geometry
`ST_StartPoint()`	Start Point of LineString
`ST_SwapXY()`	Return argument with X/Y coordinates swapped
`ST_SymDifference()`	Return point set symmetric difference of two geometries
`ST_Touches()`	Whether one geometry touches another
`ST_Transform()`	Transform coordinates of geometry
`ST_Union()`	Return point set union of two geometries
`ST_Validate()`	Return validated geometry
`ST_Within()`	Whether one geometry is within another
`ST_X()`	Return X coordinate of Point
`ST_Y()`	Return Y coordinate of Point
`STATEMENT_DIGEST()`	Compute statement digest hash value
`STATEMENT_DIGEST_TEXT()`	Compute normalized statement digest
`STD()`	Return the population standard deviation
`STDDEV()`	Return the population standard deviation
`STDDEV_POP()`	Return the population standard deviation
`STDDEV_SAMP()`	Return the sample standard deviation
`STR_TO_DATE()`	Convert a string to a date
`STRCMP()`	Compare two strings
`SUBDATE()`	Synonym for DATE_SUB() when invoked with three arguments
`SUBSTR()`	Return the substring as specified
`SUBSTRING()`	Return the substring as specified
`SUBSTRING_INDEX()`	Return a substring from a string before the specified number of occurrences of the delimiter
`SUBTIME()`	Subtract times
`SUM()`	Return the sum
`SYSDATE()`	Return the time at which the function executes
`SYSTEM_USER()`	Synonym for USER()
`TAN()`	Return the tangent of the argument
`TIME()`	Extract the time portion of the expression passed
`TIME_FORMAT()`	Format as time
`TIME_TO_SEC()`	Return the argument converted to seconds
`TIMEDIFF()`	Subtract time
`*`	Multiplication operator
`TIMESTAMP()`	With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
`TIMESTAMPADD()`	Add an interval to a datetime expression
`TIMESTAMPDIFF()`	Subtract an interval from a datetime expression
`TO_BASE64()`	Return the argument converted to a base-64 string
`TO_DAYS()`	Return the date argument converted to days
`TO_SECONDS()`	Return the date or datetime argument converted to seconds since Year 0
`TRIM()`	Remove leading and trailing spaces
`TRUNCATE()`	Truncate to specified number of decimal places
`UCASE()`	Synonym for UPPER()
`-`	Change the sign of the argument
`UNCOMPRESS()`	Uncompress a string compressed
`UNCOMPRESSED_LENGTH()`	Return the length of a string before compression
`UNHEX()`	Return a string containing hex representation of a number
`UNIX_TIMESTAMP()`	Return a Unix timestamp
`UpdateXML()`	Return replaced XML fragment
`UPPER()`	Convert to uppercase
`USER()`	The user name and host name provided by the client
`UTC_DATE()`	Return the current UTC date
`UTC_TIME()`	Return the current UTC time
`UTC_TIMESTAMP()`	Return the current UTC date and time
`UUID()`	Return a Universal Unique Identifier (UUID)
`UUID_SHORT()`	Return an integer-valued universal identifier
`UUID_TO_BIN()`	Convert string UUID to binary
`VALIDATE_PASSWORD_STRENGTH()`	Determine strength of password
`VALUES()`	Defines the values to be used during an INSERT
`VAR_POP()`	Return the population standard variance
`VAR_SAMP()`	Return the sample variance
`VARIANCE()`	Return the population standard variance
`VERSION()`	Return a string that indicates the MySQL server version
`WAIT_FOR_EXECUTED_GTID_SET()`	Wait until the given GTIDs have executed on slave.
`WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()`	Wait until the given GTIDs have executed on slave.
`WEEK()`	Return the week number
`WEEKDAY()`	Return the weekday index
`WEEKOFYEAR()`	Return the calendar week of the date (1-53)
`WEIGHT_STRING()`	Return the weight string for a string
`XOR`	Logical XOR
`YEAR()`	Return the year
`YEARWEEK()`	Return the year and week

12.2 Type Conversion in Expression Evaluation

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa.

mysql> SELECT 1+'1';
        -> 2
mysql> SELECT CONCAT(2,' test');
        -> '2 test'

It is also possible to convert a number to a string explicitly using the CAST() function. Conversion occurs implicitly with the CONCAT() function because it expects string arguments.

mysql> SELECT 38.8, CAST(38.8 AS CHAR);
        -> 38.8, '38.8'
mysql> SELECT 38.8, CONCAT(38.8);
        -> 38.8, '38.8'

See later in this section for information about the character set of implicit number-to-string conversions, and for modified rules that apply to CREATE TABLE ... SELECT statements.

The following rules describe how conversion occurs for comparison operations:

If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.
If both arguments in a comparison operation are strings, they are compared as strings.
If both arguments are integers, they are compared as integers.
Hexadecimal values are treated as binary strings if not compared to a number.
If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. This is not done for the arguments to IN(). To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.
If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
In all other cases, the arguments are compared as floating-point (real) numbers.

For information about conversion of values from one temporal type to another, see Section 11.3.7, “Conversion Between Date and Time Types”.

Comparison of JSON values takes place at two levels. The first level of comparison is based on the JSON types of the compared values. If the types differ, the comparison result is determined solely by which type has higher precedence. If the two values have the same JSON type, a second level of comparison occurs using type-specific rules. For comparison of JSON and non-JSON values, the non-JSON value is converted to JSON and the values compared as JSON values. For details, see Comparison and Ordering of JSON Values.

The following examples illustrate conversion of strings to numbers for comparison operations:

mysql> SELECT 1 > '6x';
        -> 0
mysql> SELECT 7 > '6x';
        -> 1
mysql> SELECT 0 > 'x6';
        -> 0
mysql> SELECT 0 = 'x6';
        -> 1

For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:

SELECT * FROM tbl_name WHERE str_col=1;

The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'.

Comparisons that use floating-point numbers (or values that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent:

mysql> SELECT '18015376320243458' = 18015376320243458;
        -> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
        -> 0

Such results can occur because the values are converted to floating-point numbers, which have only 53 bits of precision and are subject to rounding:

mysql> SELECT '18015376320243459'+0.0;
        -> 1.8015376320243e+16

Furthermore, the conversion from string to floating-point and from integer to floating-point do not necessarily occur the same way. The integer may be converted to floating-point by the CPU, whereas the string is converted digit by digit in an operation that involves floating-point multiplications.

The results shown will vary on different systems, and can be affected by factors such as computer architecture or the compiler version or optimization level. One way to avoid such problems is to use CAST() so that a value is not converted implicitly to a float-point number:

mysql> SELECT CAST('18015376320243459' AS UNSIGNED) = 18015376320243459;
        -> 1

For more information about floating-point comparisons, see Section B.5.4.8, “Problems with Floating-Point Values”.

The server includes dtoa, a conversion library that provides the basis for improved conversion between string or DECIMAL values and approximate-value (FLOAT/DOUBLE) numbers:

Consistent conversion results across platforms, which eliminates, for example, Unix versus Windows conversion differences.
Accurate representation of values in cases where results previously did not provide sufficient precision, such as for values close to IEEE limits.
Conversion of numbers to string format with the best possible precision. The precision of dtoa is always the same or better than that of the standard C library functions.

Because the conversions produced by this library differ in some cases from non-dtoa results, the potential exists for incompatibilities in applications that rely on previous results. For example, applications that depend on a specific exact result from previous conversions might need adjustment to accommodate additional precision.

The dtoa library provides conversions with the following properties. D represents a value with a DECIMAL or string representation, and F represents a floating-point number in native binary (IEEE) format.

F -> D conversion is done with the best possible precision, returning D as the shortest string that yields F when read back in and rounded to the nearest value in native binary format as specified by IEEE.
D -> F conversion is done such that F is the nearest native binary number to the input decimal string D.

These properties imply that F -> D -> F conversions are lossless unless F is -inf, +inf, or NaN. The latter values are not supported because the SQL standard defines them as invalid values for FLOAT or DOUBLE.

For D -> F -> D conversions, a sufficient condition for losslessness is that D uses 15 or fewer digits of precision, is not a denormal value, -inf, +inf, or NaN. In some cases, the conversion is lossless even if D has more than 15 digits of precision, but this is not always the case.

Implicit conversion of a numeric or temporal value to string produces a value that has a character set and collation determined by the character_set_connection and collation_connection system variables. (These variables commonly are set with SET NAMES. For information about connection character sets, see Section 10.4, “Connection Character Sets and Collations”.)

This means that such a conversion results in a character (nonbinary) string (a CHAR, VARCHAR, or LONGTEXT value), except in the case that the connection character set is set to binary. In that case, the conversion result is a binary string (a BINARY, VARBINARY, or LONGBLOB value).

For integer expressions, the preceding remarks about expression evaluation apply somewhat differently for expression assignment; for example, in a statement such as this:

CREATE TABLE t SELECT integer_expr;

In this case, the table in the column resulting from the expression has type INT or BIGINT depending on the length of the integer expression. If the maximum length of the expression does not fit in an INT, BIGINT is used instead. The length is taken from the max_length value of the SELECT result set metadata (see Section 27.7.5, “C API Data Structures”). This means that you can force a BIGINT rather than INT by use of a sufficiently long expression:

CREATE TABLE t SELECT 000000000000000000000;

12.3 Operators

12.3.1 Operator Precedence
12.3.2 Comparison Functions and Operators
12.3.3 Logical Operators
12.3.4 Assignment Operators

Table 12.2 Operators

Name	Description
`AND`, `&&`	Logical AND
`=`	Assign a value (as part of a `SET` statement, or as part of the `SET` clause in an `UPDATE` statement)
`:=`	Assign a value
`BETWEEN ... AND ...`	Check whether a value is within a range of values
`BINARY`	Cast a string to a binary string
`&`	Bitwise AND
`~`	Bitwise inversion
`\|`	Bitwise OR
`^`	Bitwise XOR
`CASE`	Case operator
`DIV`	Integer division
`/`	Division operator
`=`	Equal operator
`<=>`	NULL-safe equal to operator
`>`	Greater than operator
`>=`	Greater than or equal operator
`IS`	Test a value against a boolean
`IS NOT`	Test a value against a boolean
`IS NOT NULL`	NOT NULL value test
`IS NULL`	NULL value test
`->`	Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
`->>`	Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
`<<`	Left shift
`<`	Less than operator
`<=`	Less than or equal operator
`LIKE`	Simple pattern matching
`-`	Minus operator
`%`, `MOD`	Modulo operator
`NOT`, `!`	Negates value
`NOT BETWEEN ... AND ...`	Check whether a value is not within a range of values
`!=`, `<>`	Not equal operator
`NOT LIKE`	Negation of simple pattern matching
`NOT REGEXP`	Negation of REGEXP
`\|\|`, `OR`	Logical OR
`+`	Addition operator
`REGEXP`	Whether string matches regular expression
`>>`	Right shift
`RLIKE`	Whether string matches regular expression
`SOUNDS LIKE`	Compare sounds
`*`	Multiplication operator
`-`	Change the sign of the argument
`XOR`	Logical XOR

12.3.1 Operator Precedence

Operator precedences are shown in the following list, from highest precedence to the lowest. Operators that are shown together on a line have the same precedence.

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
AND, &&
XOR
OR, ||
= (assignment), :=

The precedence of = depends on whether it is used as a comparison operator (=) or as an assignment operator (=). When used as a comparison operator, it has the same precedence as <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, and IN. When used as an assignment operator, it has the same precedence as :=. Section 13.7.5.1, “SET Syntax for Variable Assignment”, and Section 9.4, “User-Defined Variables”, explain how MySQL determines which interpretation of = should apply.

For operators that occur at the same precedence level within an expression, evaluation proceeds left to right, with the exception that assignments evaluate right to left.

The meaning of some operators depends on the SQL mode:

By default, || is a logical OR operator. With PIPES_AS_CONCAT enabled, || is string concatenation, with a precedence between ^ and the unary operators.
By default, ! has a higher precedence than NOT. With HIGH_NOT_PRECEDENCE enabled, ! and NOT have the same precedence.

See Section 5.1.10, “Server SQL Modes”.

The precedence of operators determines the order of evaluation of terms in an expression. To override this order and group terms explicitly, use parentheses. For example:

mysql> SELECT 1+2*3;
        -> 7
mysql> SELECT (1+2)*3;
        -> 9

12.3.2 Comparison Functions and Operators

Table 12.3 Comparison Operators

Name	Description
`BETWEEN ... AND ...`	Check whether a value is within a range of values
`COALESCE()`	Return the first non-NULL argument
`=`	Equal operator
`<=>`	NULL-safe equal to operator
`>`	Greater than operator
`>=`	Greater than or equal operator
`GREATEST()`	Return the largest argument
`IN()`	Check whether a value is within a set of values
`INTERVAL()`	Return the index of the argument that is less than the first argument
`IS`	Test a value against a boolean
`IS NOT`	Test a value against a boolean
`IS NOT NULL`	NOT NULL value test
`IS NULL`	NULL value test
`ISNULL()`	Test whether the argument is NULL
`LEAST()`	Return the smallest argument
`<`	Less than operator
`<=`	Less than or equal operator
`LIKE`	Simple pattern matching
`NOT BETWEEN ... AND ...`	Check whether a value is not within a range of values
`!=`, `<>`	Not equal operator
`NOT IN()`	Check whether a value is not within a set of values
`NOT LIKE`	Negation of simple pattern matching
`STRCMP()`	Compare two strings

Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL. These operations work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as necessary.

The following relational comparison operators can be used to compare not only scalar operands, but row operands:

=  >  <  >=  <=  <>  !=

The descriptions for those operators later in this section detail how they work with row operands. For additional examples of row comparisons in the context of row subqueries, see Section 13.2.11.5, “Row Subqueries”.

Some of the functions in this section return values other than 1 (TRUE), 0 (FALSE), or NULL. LEAST() and GREATEST() are examples of such functions; Section 12.2, “Type Conversion in Expression Evaluation”, describes the rules for comparison operations performed by these and similar functions for determining their return values.

Note

In previous versions of MySQL, when evaluating an expression containing LEAST() or GREATEST(), the server attempted to guess the context in which the function was used, and to coerce the function's arguments to the data type of the expression as a whole. For example, the arguments to LEAST("11", "45", "2") are evaluated and sorted as strings, so that this expression returns "11". In MySQL 8.0.3 and earlier, when evaluating the expression LEAST("11", "45", "2") + 0, the server converted the arguments to integers (anticipating the addition of integer 0 to the result) before sorting them, thus returning 2.

Beginning with MySQL 8.0.4, the server no longer attempts to infer context in this fashion. Instead, the function is executed using the arguments as provided, performing data type conversions to one or more of the arguments if and only if they are not all of the same type. Any type coercion mandated by an expression that makes use of the return value is now performed following function execution. This means that, in MySQl 8.0.4 and later, LEAST("11", "45", "2") + 0 evaluates to "11" + 0 and thus to integer 11. (Bug #83895, Bug #25123839)

To convert a value to a specific type for comparison purposes, you can use the CAST() function. String values can be converted to a different character set using CONVERT(). See Section 12.10, “Cast Functions and Operators”.

By default, string comparisons are not case-sensitive and use the current character set. The default is utf8mb4.

=

Equal:

mysql> SELECT 1 = 0;
        -> 0
mysql> SELECT '0' = 0;
        -> 1
mysql> SELECT '0.0' = 0;
        -> 1
mysql> SELECT '0.01' = 0;
        -> 0
mysql> SELECT '.01' = 0.01;
        -> 1

For row comparisons, (a, b) = (x, y) is equivalent to:

(a = x) AND (b = y)

<=>
NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.
The <=> operator is equivalent to the standard SQL IS NOT DISTINCT FROM operator.
```
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL
```
For row comparisons, (a, b) <=> (x, y) is equivalent to:
```
(a <=> x) AND (b <=> y)
```

<>, !=

Not equal:

mysql> SELECT '.01' <> '0.01';
        -> 1
mysql> SELECT .01 <> '0.01';
        -> 0
mysql> SELECT 'zapp' <> 'zappp';
        -> 1

For row comparisons, (a, b) <> (x, y) and (a, b) != (x, y) are equivalent to:

(a <> x) OR (b <> y)

<=

Less than or equal:

mysql> SELECT 0.1 <= 2;
        -> 1

For row comparisons, (a, b) <= (x, y) is equivalent to:

(a < x) OR ((a = x) AND (b <= y))

<

Less than:

mysql> SELECT 2 < 2;
        -> 0

For row comparisons, (a, b) < (x, y) is equivalent to:

(a < x) OR ((a = x) AND (b < y))

>=

Greater than or equal:

mysql> SELECT 2 >= 2;
        -> 1

For row comparisons, (a, b) >= (x, y) is equivalent to:

(a > x) OR ((a = x) AND (b >= y))

>

Greater than:

mysql> SELECT 2 > 2;
        -> 0

For row comparisons, (a, b) > (x, y) is equivalent to:

(a > x) OR ((a = x) AND (b > y))

IS boolean_value
Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.
```
mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
        -> 1, 1, 1
```
IS NOT boolean_value
Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.
```
mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
        -> 1, 1, 0
```
IS NULL
Tests whether a value is NULL.
```
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
        -> 0, 0, 1
```
To work well with ODBC programs, MySQL supports the following extra features when using IS NULL:
- If sql_auto_is_null variable is set to 1, then after a statement that successfully inserts an automatically generated AUTO_INCREMENT value, you can find that value by issuing a statement of the following form:
```
SELECT * FROM tbl_name WHERE auto_col IS NULL
```
  If the statement returns a row, the value returned is the same as if you invoked the LAST_INSERT_ID() function. For details, including the return value after a multiple-row insert, see Section 12.14, “Information Functions”. If no AUTO_INCREMENT value was successfully inserted, the SELECT statement returns no row.
  The behavior of retrieving an AUTO_INCREMENT value by using an IS NULL comparison can be disabled by setting sql_auto_is_null = 0. See Section 5.1.7, “Server System Variables”.
  The default value of sql_auto_is_null is 0.
- For DATE and DATETIME columns that are declared as NOT NULL, you can find the special date '0000-00-00' by using a statement like this:
```
SELECT * FROM tbl_name WHERE date_column IS NULL
```
  This is needed to get some ODBC applications to work because ODBC does not support a '0000-00-00' date value.
  See Obtaining Auto-Increment Values, and the description for the FLAG_AUTO_IS_NULL option at Connector/ODBC Connection Parameters.

IS NOT NULL

Tests whether a value is not NULL.

mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
        -> 1, 1, 0

expr BETWEEN min AND max
If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 12.2, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.
```
mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
        -> 1, 0
mysql> SELECT 1 BETWEEN 2 AND 3;
        -> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
        -> 0
```
For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.
expr NOT BETWEEN min AND max
This is the same as NOT (expr BETWEEN min AND max).
COALESCE(value,...)
Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.
The return type of COALESCE() is the aggregated type of the argument types.
```
mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL
```
GREATEST(value1,value2,...)
With two or more arguments, returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST().
```
mysql> SELECT GREATEST(2,0);
        -> 2
mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
        -> 767.0
mysql> SELECT GREATEST('B','A','C');
        -> 'C'
```
GREATEST() returns NULL if any argument is NULL.
expr IN (value,...)
Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. Otherwise, type conversion takes place according to the rules described in Section 12.2, “Type Conversion in Expression Evaluation”, but applied to all the arguments.
```
mysql> SELECT 2 IN (0,3,5,7);
        -> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
        -> 1
```
IN can be used to compare row constructors:
```
mysql> SELECT (3,4) IN ((1,2), (3,4));
        -> 1
mysql> SELECT (3,4) IN ((1,2), (3,5));
        -> 0
```
You should never mix quoted and unquoted values in an IN list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results. For example, do not write an IN expression like this:
```
SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');
```
Instead, write it like this:
```
SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');
```
The number of values in the IN list is only limited by the max_allowed_packet value.
To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.
IN() syntax can also be used to write certain types of subqueries. See Section 13.2.11.3, “Subqueries with ANY, IN, or SOME”.
expr NOT IN (value,...)
This is the same as NOT (expr IN (value,...)).
ISNULL(expr)
If expr is NULL, ISNULL() returns 1, otherwise it returns 0.
```
mysql> SELECT ISNULL(1+1);
        -> 0
mysql> SELECT ISNULL(1/0);
        -> 1
```
ISNULL() can be used instead of = to test whether a value is NULL. (Comparing a value to NULL using = always yields NULL.)
The ISNULL() function shares some special behaviors with the IS NULL comparison operator. See the description of IS NULL.
INTERVAL(N,N1,N2,N3,...)
Returns 0 if N < N1, 1 if N < N2 and so on or -1 if N is NULL. All arguments are treated as integers. It is required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This is because a binary search is used (very fast).
```
mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
        -> 3
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
        -> 2
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
        -> 0
```
LEAST(value1,value2,...)
With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:
- If any argument is NULL, the result is NULL. No comparison is needed.
- If all arguments are integer-valued, they are compared as integers.
- If at least one argument is double precision, they are compared as double-precision values. Otherwise, if at least one argument is a DECIMAL value, they are compared as DECIMAL values.
- If the arguments comprise a mix of numbers and strings, they are compared as numbers.
- If any argument is a nonbinary (character) string, the arguments are compared as nonbinary strings.
- In all other cases, the arguments are compared as binary strings.
The return type of LEAST() is the aggregated type of the comparison argument types.
```
mysql> SELECT LEAST(2,0);
        -> 0
mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
        -> 3.0
mysql> SELECT LEAST('B','A','C');
        -> 'A'
```

12.3.3 Logical Operators

Table 12.4 Logical Operators

Name	Description
`AND`, `&&`	Logical AND
`NOT`, `!`	Negates value
`\|\|`, `OR`	Logical OR
`XOR`	Logical XOR

In SQL, all logical operators evaluate to TRUE, FALSE, or NULL (UNKNOWN). In MySQL, these are implemented as 1 (TRUE), 0 (FALSE), and NULL. Most of this is common to different SQL database servers, although some servers may return any nonzero value for TRUE.

MySQL evaluates any nonzero, non-NULL value to TRUE. For example, the following statements all assess to TRUE:

mysql> SELECT 10 IS TRUE;
-> 1
mysql> SELECT -10 IS TRUE;
-> 1
mysql> SELECT 'string' IS NOT NULL;
-> 1

NOT, !

Logical NOT. Evaluates to 1 if the operand is 0, to 0 if the operand is nonzero, and NOT NULL returns NULL.

mysql> SELECT NOT 10;
        -> 0
mysql> SELECT NOT 0;
        -> 1
mysql> SELECT NOT NULL;
        -> NULL
mysql> SELECT ! (1+1);
        -> 0
mysql> SELECT ! 1+1;
        -> 1

The last example produces 1 because the expression evaluates the same way as (!1)+1.

AND, &&

Logical AND. Evaluates to 1 if all operands are nonzero and not NULL, to 0 if one or more operands are 0, otherwise NULL is returned.

mysql> SELECT 1 AND 1;
        -> 1
mysql> SELECT 1 AND 0;
        -> 0
mysql> SELECT 1 AND NULL;
        -> NULL
mysql> SELECT 0 AND NULL;
        -> 0
mysql> SELECT NULL AND 0;
        -> 0

OR, ||
Logical OR. When both operands are non-NULL, the result is 1 if any operand is nonzero, and 0 otherwise. With a NULL operand, the result is 1 if the other operand is nonzero, and NULL otherwise. If both operands are NULL, the result is NULL.
```
mysql> SELECT 1 OR 1;
        -> 1
mysql> SELECT 1 OR 0;
        -> 1
mysql> SELECT 0 OR 0;
        -> 0
mysql> SELECT 0 OR NULL;
        -> NULL
mysql> SELECT 1 OR NULL;
        -> 1
```
XOR
Logical XOR. Returns NULL if either operand is NULL. For non-NULL operands, evaluates to 1 if an odd number of operands is nonzero, otherwise 0 is returned.
```
mysql> SELECT 1 XOR 1;
        -> 0
mysql> SELECT 1 XOR 0;
        -> 1
mysql> SELECT 1 XOR NULL;
        -> NULL
mysql> SELECT 1 XOR 1 XOR 1;
        -> 1
```
a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b).

12.3.4 Assignment Operators

Table 12.5 Assignment Operators

Name	Description
`=`	Assign a value (as part of a `SET` statement, or as part of the `SET` clause in an `UPDATE` statement)
`:=`	Assign a value

:=
Assignment operator. Causes the user variable on the left hand side of the operator to take on the value to its right. The value on the right hand side may be a literal value, another variable storing a value, or any legal expression that yields a scalar value, including the result of a query (provided that this value is a scalar value). You can perform multiple assignments in the same SET statement. You can perform multiple assignments in the same statement.
Unlike =, the := operator is never interpreted as a comparison operator. This means you can use := in any valid SQL statement (not just in SET statements) to assign a value to a variable.
```
mysql> SELECT @var1, @var2;
        -> NULL, NULL
mysql> SELECT @var1 := 1, @var2;
        -> 1, NULL
mysql> SELECT @var1, @var2;
        -> 1, NULL
mysql> SELECT @var1, @var2 := @var1;
        -> 1, 1
mysql> SELECT @var1, @var2;
        -> 1, 1

mysql> SELECT @var1:=COUNT(*) FROM t1;
        -> 4
mysql> SELECT @var1;
        -> 4
```
You can make value assignments using := in other statements besides SELECT, such as UPDATE, as shown here:
```
mysql> SELECT @var1;
        -> 4
mysql> SELECT * FROM t1;
        -> 1, 3, 5, 7

mysql> UPDATE t1 SET c1 = 2 WHERE c1 = @var1:= 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT @var1;
        -> 1
mysql> SELECT * FROM t1;
        -> 2, 3, 5, 7
```
While it is also possible both to set and to read the value of the same variable in a single SQL statement using the := operator, this is not recommended. Section 9.4, “User-Defined Variables”, explains why you should avoid doing this.
=
This operator is used to perform value assignments in two cases, described in the next two paragraphs.
Within a SET statement, = is treated as an assignment operator that causes the user variable on the left hand side of the operator to take on the value to its right. (In other words, when used in a SET statement, = is treated identically to :=.) The value on the right hand side may be a literal value, another variable storing a value, or any legal expression that yields a scalar value, including the result of a query (provided that this value is a scalar value). You can perform multiple assignments in the same SET statement.
In the SET clause of an UPDATE statement, = also acts as an assignment operator; in this case, however, it causes the column named on the left hand side of the operator to assume the value given to the right, provided any WHERE conditions that are part of the UPDATE are met. You can make multiple assignments in the same SET clause of an UPDATE statement.
In any other context, = is treated as a comparison operator.
```
mysql> SELECT @var1, @var2;
        -> NULL, NULL
mysql> SELECT @var1 := 1, @var2;
        -> 1, NULL
mysql> SELECT @var1, @var2;
        -> 1, NULL
mysql> SELECT @var1, @var2 := @var1;
        -> 1, 1
mysql> SELECT @var1, @var2;
        -> 1, 1
```
For more information, see Section 13.7.5.1, “SET Syntax for Variable Assignment”, Section 13.2.12, “UPDATE Syntax”, and Section 13.2.11, “Subquery Syntax”.

12.4 Control Flow Functions

Table 12.6 Flow Control Operators

Name	Description
`CASE`	Case operator
`IF()`	If/else construct
`IFNULL()`	Null if/else construct
`NULLIF()`	Return NULL if expr1 = expr2

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
The first CASE syntax returns the result for the first value=compare_value comparison that is true. The second syntax returns the result for the first condition that is true. If no comparison or condition is true, the result after ELSE is returned, or NULL if there is no ELSE part.

Note

The syntax of the CASE expression described here differs slightly from that of the SQL CASE statement described in Section 13.6.5.1, “CASE Syntax”, for use inside stored programs. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.

The return type of a CASE expression result is the aggregated type of all result values:
- If all types are numeric, the aggregated type is also numeric:
  - If at least one argument is double precision, the result is double precision.
  - Otherwise, if at least one argument is DECIMAL, the result is DECIMAL.
  - Otherwise, the result is an integer type (with one exception):
    
    If all integer types are all signed or all unsigned, the result is the same sign and the precision is the highest of all specified integer types (that is, TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT).
    If there is a combination of signed and unsigned integer types, the result is signed and the precision may be higher. For example, if the types are signed INT and unsigned INT, the result is signed BIGINT.
    The exception is unsigned BIGINT combined with any signed integer type. The result is DECIMAL with sufficient precision and scale 0.
- If all types are BIT, the result is BIT. Otherwise, BIT arguments are treated similar to BIGINT.
- If all types are YEAR, the result is YEAR. Otherwise, YEAR arguments are treated similar to INT.
- If all types are character string (CHAR or VARCHAR), the result is VARCHAR with maximum length determined by the longest character length of the operands.
- If all types are character or binary string, the result is VARBINARY.
- SET and ENUM are treated similar to VARCHAR; the result is VARCHAR.
- If all types are JSON, the result is JSON.
- If all types are temporal, the result is temporal:
  - If all temporal types are DATE, TIME, or TIMESTAMP, the result is DATE, TIME, or TIMESTAMP, respectively.
  - Otherwise, for a mix of temporal types, the result is DATETIME.
- If all types are GEOMETRY, the result is GEOMETRY.
- If any type is BLOB, the result is BLOB.
- For all other type combinations, the result is VARCHAR.
- Literal NULL operands are ignored for type aggregation.
```
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
    ->     WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
        -> 'true'
mysql> SELECT CASE BINARY 'B'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULL
```
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3.

Note

There is also an IF statement, which differs from the IF() function described here. See Section 13.6.5.2, “IF Syntax”.

If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the type of the non-NULL expression.
The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows:
- If expr2 or expr3 produce a string, the result is a string.
  If expr2 and expr3 are both strings, the result is case-sensitive if either string is case sensitive.
- If expr2 or expr3 produce a floating-point value, the result is a floating-point value.
- If expr2 or expr3 produce an integer, the result is an integer.
```
mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'yes','no');
        -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
        -> 'no'
```

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

mysql> SELECT IFNULL(1,0);
        -> 1
mysql> SELECT IFNULL(NULL,10);
        -> 10
mysql> SELECT IFNULL(1/0,10);
        -> 10
mysql> SELECT IFNULL(1/0,'yes');
        -> 'yes'

The default return type of IFNULL(expr1,expr2) is the more “general” of the two expressions, in the order STRING, REAL, or INTEGER. Consider the case of a table based on expressions or where MySQL must internally store a value returned by IFNULL() in a temporary table:


mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
mysql> DESCRIBE tmp;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| test  | varbinary(4) | NO   |     |         |       |
+-------+--------------+------+-----+---------+-------+

In this example, the type of the test column is VARBINARY(4) (a string type).

NULLIF(expr1,expr2)
Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
The return value has the same type as the first argument.
```
mysql> SELECT NULLIF(1,1);
        -> NULL
mysql> SELECT NULLIF(1,2);
        -> 1
```
Note

MySQL evaluates expr1 twice if the arguments are not equal.

12.5 String Functions

12.5.1 String Comparison Functions
12.5.2 Regular Expressions
12.5.3 Character Set and Collation of Function Results

Table 12.7 String Operators

Name	Description
`ASCII()`	Return numeric value of left-most character
`BIN()`	Return a string containing binary representation of a number
`BIT_LENGTH()`	Return length of argument in bits
`CHAR()`	Return the character for each integer passed
`CHAR_LENGTH()`	Return number of characters in argument
`CHARACTER_LENGTH()`	Synonym for CHAR_LENGTH()
`CONCAT()`	Return concatenated string
`CONCAT_WS()`	Return concatenate with separator
`ELT()`	Return string at index number
`EXPORT_SET()`	Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
`FIELD()`	Return the index (position) of the first argument in the subsequent arguments
`FIND_IN_SET()`	Return the index position of the first argument within the second argument
`FORMAT()`	Return a number formatted to specified number of decimal places
`FROM_BASE64()`	Decode base64 encoded string and return result
`HEX()`	Return a hexadecimal representation of a decimal or string value
`INSERT()`	Insert a substring at the specified position up to the specified number of characters
`INSTR()`	Return the index of the first occurrence of substring
`LCASE()`	Synonym for LOWER()
`LEFT()`	Return the leftmost number of characters as specified
`LENGTH()`	Return the length of a string in bytes
`LIKE`	Simple pattern matching
`LOAD_FILE()`	Load the named file
`LOCATE()`	Return the position of the first occurrence of substring
`LOWER()`	Return the argument in lowercase
`LPAD()`	Return the string argument, left-padded with the specified string
`LTRIM()`	Remove leading spaces
`MAKE_SET()`	Return a set of comma-separated strings that have the corresponding bit in bits set
`MATCH`	Perform full-text search
`MID()`	Return a substring starting from the specified position
`NOT LIKE`	Negation of simple pattern matching
`NOT REGEXP`	Negation of REGEXP
`OCT()`	Return a string containing octal representation of a number
`OCTET_LENGTH()`	Synonym for LENGTH()
`ORD()`	Return character code for leftmost character of the argument
`POSITION()`	Synonym for LOCATE()
`QUOTE()`	Escape the argument for use in an SQL statement
`REGEXP`	Whether string matches regular expression
`REGEXP_INSTR()`	Starting index of substring matching regular expression
`REGEXP_LIKE()`	Whether string matches regular expression
`REGEXP_REPLACE()`	Replace substrings matching regular expression
`REGEXP_SUBSTR()`	Return substring matching regular expression
`REPEAT()`	Repeat a string the specified number of times
`REPLACE()`	Replace occurrences of a specified string
`REVERSE()`	Reverse the characters in a string
`RIGHT()`	Return the specified rightmost number of characters
`RLIKE`	Whether string matches regular expression
`RPAD()`	Append string the specified number of times
`RTRIM()`	Remove trailing spaces
`SOUNDEX()`	Return a soundex string
`SOUNDS LIKE`	Compare sounds
`SPACE()`	Return a string of the specified number of spaces
`STRCMP()`	Compare two strings
`SUBSTR()`	Return the substring as specified
`SUBSTRING()`	Return the substring as specified
`SUBSTRING_INDEX()`	Return a substring from a string before the specified number of occurrences of the delimiter
`TO_BASE64()`	Return the argument converted to a base-64 string
`TRIM()`	Remove leading and trailing spaces
`UCASE()`	Synonym for UPPER()
`UNHEX()`	Return a string containing hex representation of a number
`UPPER()`	Convert to uppercase
`WEIGHT_STRING()`	Return the weight string for a string

String-valued functions return NULL if the length of the result would be greater than the value of the max_allowed_packet system variable. See Section 5.1.1, “Configuring the Server”.

For functions that operate on string positions, the first position is numbered 1.

For functions that take length arguments, noninteger arguments are rounded to the nearest integer.

ASCII(str)
Returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII() works for 8-bit characters.
```
mysql> SELECT ASCII('2');
        -> 50
mysql> SELECT ASCII(2);
        -> 50
mysql> SELECT ASCII('dx');
        -> 100
```
See also the ORD() function.
BIN(N)
Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,2). Returns NULL if N is NULL.
```
mysql> SELECT BIN(12);
        -> '1100'
```

BIT_LENGTH(str)

Returns the length of the string str in bits.

mysql> SELECT BIT_LENGTH('text');
        -> 32

CHAR(N,... [USING charset_name])

CHAR() interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped.

mysql> SELECT CHAR(77,121,83,81,'76');
        -> 'MySQL'
mysql> SELECT CHAR(77,77.3,'77.3');
        -> 'MMM'

CHAR() arguments larger than 255 are converted into multiple result bytes. For example, CHAR(256) is equivalent to CHAR(1,0), and CHAR(256*256) is equivalent to CHAR(1,0,0):

mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));
+----------------+----------------+
| HEX(CHAR(1,0)) | HEX(CHAR(256)) |
+----------------+----------------+
| 0100           | 0100           |
+----------------+----------------+
mysql> SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));
+------------------+--------------------+
| HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |
+------------------+--------------------+
| 010000           | 010000             |
+------------------+--------------------+

By default, CHAR() returns a binary string. To produce a string in a given character set, use the optional USING clause:

mysql> SELECT CHARSET(CHAR(X'65')), CHARSET(CHAR(X'65' USING utf8));
+----------------------+---------------------------------+
| CHARSET(CHAR(X'65')) | CHARSET(CHAR(X'65' USING utf8)) |
+----------------------+---------------------------------+
| binary               | utf8                            |
+----------------------+---------------------------------+

If USING is given and the result string is illegal for the given character set, a warning is issued. Also, if strict SQL mode is enabled, the result from CHAR() becomes NULL.

CHAR_LENGTH(str)
Returns the length of the string str, measured in characters. A multibyte character counts as a single character. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.
CHARACTER_LENGTH(str)
CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().
CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form.
CONCAT() returns NULL if any argument is NULL.
```
mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'
```
For quoted strings, concatenation can be performed by placing the strings next to each other:
```
mysql> SELECT 'My' 'S' 'QL';
        -> 'MySQL'
```
CONCAT_WS(separator,str1,str2,...)
CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.
```
mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
        -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
        -> 'First name,Last Name'
```
CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.
ELT(N,str1,str2,str3,...)
ELT() returns the Nth element of the list of strings: str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD().
```
mysql> SELECT ELT(1, 'Aa', 'Bb', 'Cc', 'Dd');
        -> 'Aa'
mysql> SELECT ELT(4, 'Aa', 'Bb', 'Cc', 'Dd');
        -> 'Dd'
```
EXPORT_SET(bits,on,off[,separator[,number_of_bits]])
Returns a string such that for every bit set in the value bits, you get an on string and for every bit not set in the value, you get an off string. Bits in bits are examined from right to left (from low-order to high-order bits). Strings are added to the result from left to right, separated by the separator string (the default being the comma character ,). The number of bits examined is given by number_of_bits, which has a default of 64 if not specified. number_of_bits is silently clipped to 64 if larger than 64. It is treated as an unsigned integer, so a value of −1 is effectively the same as 64.
```
mysql> SELECT EXPORT_SET(5,'Y','N',',',4);
        -> 'Y,N,Y,N'
mysql> SELECT EXPORT_SET(6,'1','0',',',10);
        -> '0,1,1,0,0,0,0,0,0,0'
```
FIELD(str,str1,str2,str3,...)
Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.
If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.
If str is NULL, the return value is 0 because NULL fails equality comparison with any value. FIELD() is the complement of ELT().
```
mysql> SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
        -> 2
mysql> SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
        -> 0
```
FIND_IN_SET(str,strlist)
Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by , characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (,) character.
```
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2
```
FORMAT(X,D[,locale])
Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.
The optional third parameter enables a locale to be specified to be used for the result number's decimal point, thousands separator, and grouping between separators. Permissible locale values are the same as the legal values for the lc_time_names system variable (see Section 10.15, “MySQL Server Locale Support”). If no locale is specified, the default is 'en_US'.
```
mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
        -> '12,332'
mysql> SELECT FORMAT(12332.2,2,'de_DE');
        -> '12.332,20'
```
FROM_BASE64(str)
Takes a string encoded with the base-64 encoded rules used by TO_BASE64() and returns the decoded result as a binary string. The result is NULL if the argument is NULL or not a valid base-64 string. See the description of TO_BASE64() for details about the encoding and decoding rules.
```
mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
        -> 'JWJj', 'abc'
```
HEX(str), HEX(N)
For a string argument str, HEX() returns a hexadecimal string representation of str where each byte of each character in str is converted to two hexadecimal digits. (Multibyte characters therefore become more than two digits.) The inverse of this operation is performed by the UNHEX() function.
For a numeric argument N, HEX() returns a hexadecimal string representation of the value of N treated as a longlong (BIGINT) number. This is equivalent to CONV(N,10,16). The inverse of this operation is performed by CONV(HEX(N),16,10).
```
mysql> SELECT X'616263', HEX('abc'), UNHEX(HEX('abc'));
        -> 'abc', 616263, 'abc'
mysql> SELECT HEX(255), CONV(HEX(255),16,10);
        -> 'FF', 255
```
INSERT(str,pos,len,newstr)
Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL.
```
mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
        -> 'QuWhattic'
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
        -> 'Quadratic'
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
        -> 'QuWhat'
```
This function is multibyte safe.
INSTR(str,substr)
Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.
```
mysql> SELECT INSTR('foobarbar', 'bar');
        -> 4
mysql> SELECT INSTR('xbar', 'foobar');
        -> 0
```
This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.
LCASE(str)
LCASE() is a synonym for LOWER().
LCASE() used in a view is rewritten as LOWER() when storing the view's definition. (Bug #12844279)
LEFT(str,len)
Returns the leftmost len characters from the string str, or NULL if any argument is NULL.
```
mysql> SELECT LEFT('foobarbar', 5);
        -> 'fooba'
```
This function is multibyte safe.
LENGTH(str)
Returns the length of the string str, measured in bytes. A multibyte character counts as multiple bytes. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.
```
mysql> SELECT LENGTH('text');
        -> 4
```
Note

The Length() OpenGIS spatial function is named ST_Length() in MySQL.
LOAD_FILE(file_name)
Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.
If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.
The character_set_filesystem system variable controls interpretation of file names that are given as literal strings.
```
mysql> UPDATE t
            SET blob_col=LOAD_FILE('/tmp/picture')
            WHERE id=1;
```
LOCATE(substr,str), LOCATE(substr,str,pos)
The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. Returns NULL if any argument is NULL.
```
mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
        -> 7
```
This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.
LOWER(str)
Returns the string str with all characters changed to lowercase according to the current character set mapping. The default is utf8mb4.
```
mysql> SELECT LOWER('QUADRATICALLY');
        -> 'quadratically'
```
LOWER() (and UPPER()) are ineffective when applied to binary strings (BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the string to a nonbinary string:
```
mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING utf8mb4));
+-------------+------------------------------------+
| LOWER(@str) | LOWER(CONVERT(@str USING utf8mb4)) |
+-------------+------------------------------------+
| New York    | new york                           |
+-------------+------------------------------------+
```
For collations of Unicode character sets, LOWER() and UPPER() work according to the Unicode Collation Algorithm (UCA) version in the collation name, if there is one, and UCA 4.0.0 if no version is specified. For example, utf8mb4_0900_ai_ci and utf8_unicode_520_ci work according to UCA 9.0.0 and 5.2.0, respectively, whereas utf8_unicode_ci works according to UCA 4.0.0. See Section 10.10.1, “Unicode Character Sets”.
This function is multibyte safe.
LCASE() used within views is rewritten as LOWER().
LPAD(str,len,padstr)
Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.
```
mysql> SELECT LPAD('hi',4,'??');
        -> '??hi'
mysql> SELECT LPAD('hi',1,'??');
        -> 'h'
```
LTRIM(str)
Returns the string str with leading space characters removed.
```
mysql> SELECT LTRIM('  barbar');
        -> 'barbar'
```
This function is multibyte safe.
MAKE_SET(bits,str1,str2,...)
Returns a set value (a string containing substrings separated by , characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1, str2, ... are not appended to the result.
```
mysql> SELECT MAKE_SET(1,'a','b','c');
        -> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
        -> 'hello,world'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
        -> 'hello'
mysql> SELECT MAKE_SET(0,'a','b','c');
        -> ''
```
MID(str,pos,len)
MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).
OCT(N)
Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL.
```
mysql> SELECT OCT(12);
        -> '14'
```
OCTET_LENGTH(str)
OCTET_LENGTH() is a synonym for LENGTH().
ORD(str)
If the leftmost character of the string str is a multibyte character, returns the code for that character, calculated from the numeric values of its constituent bytes using this formula:
```
  (1st byte code)
+ (2nd byte code * 256)
+ (3rd byte code * 256^2) ...
```
If the leftmost character is not a multibyte character, ORD() returns the same value as the ASCII() function.
```
mysql> SELECT ORD('2');
        -> 50
```
POSITION(substr IN str)
POSITION(substr IN str) is a synonym for LOCATE(substr,str).
QUOTE(str)
Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotation marks and with each instance of backslash (\), single quote ('), ASCII NUL, and Control+Z preceded by a backslash. If the argument is NULL, the return value is the word “NULL” without enclosing single quotation marks.
```
mysql> SELECT QUOTE('Don\'t!');
        -> 'Don\'t!'
mysql> SELECT QUOTE(NULL);
        -> NULL
```
For comparison, see the quoting rules for literal strings and within the C API in Section 9.1.1, “String Literals”, and Section 27.7.7.56, “mysql_real_escape_string_quote()”.
REPEAT(str,count)
Returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL.
```
mysql> SELECT REPEAT('MySQL', 3);
        -> 'MySQLMySQLMySQL'
```
REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.
```
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'
```
This function is multibyte safe.
REVERSE(str)
Returns the string str with the order of the characters reversed.
```
mysql> SELECT REVERSE('abc');
        -> 'cba'
```
This function is multibyte safe.
RIGHT(str,len)
Returns the rightmost len characters from the string str, or NULL if any argument is NULL.
```
mysql> SELECT RIGHT('foobarbar', 4);
        -> 'rbar'
```
This function is multibyte safe.
RPAD(str,len,padstr)
Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.
```
mysql> SELECT RPAD('hi',5,'?');
        -> 'hi???'
mysql> SELECT RPAD('hi',1,'?');
        -> 'h'
```
This function is multibyte safe.
RTRIM(str)
Returns the string str with trailing space characters removed.
```
mysql> SELECT RTRIM('barbar   ');
        -> 'barbar'
```
This function is multibyte safe.
SOUNDEX(str)
Returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is four characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All nonalphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.

Important

When using SOUNDEX(), you should be aware of the following limitations:
- This function, as currently implemented, is intended to work well with strings that are in the English language only. Strings in other languages may not produce reliable results.
- This function is not guaranteed to provide consistent results with strings that use multibyte character sets, including utf-8. See Bug #22638 for more information.
```
mysql> SELECT SOUNDEX('Hello');
        -> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
        -> 'Q36324'
```
Note

This function implements the original Soundex algorithm, not the more popular enhanced version (also described by D. Knuth). The difference is that original version discards vowels first and duplicates second, whereas the enhanced version discards duplicates first and vowels second.
expr1 SOUNDS LIKE expr2
This is the same as SOUNDEX(expr1) = SOUNDEX(expr2).
SPACE(N)
Returns a string consisting of N space characters.
```
mysql> SELECT SPACE(6);
        -> '      '
```
SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len)
SUBSTR() is a synonym for SUBSTRING().
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)
The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.
For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.
```
mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'
```
This function is multibyte safe.
If len is less than 1, the result is the empty string.
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.
```
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'
```
This function is multibyte safe.
TO_BASE64(str)
Converts the string argument to base-64 encoded form and returns the result as a character string with the connection character set and collation. If the argument is not a string, it is converted to a string before conversion takes place. The result is NULL if the argument is NULL. Base-64 encoded strings can be decoded using the FROM_BASE64() function.
```
mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
        -> 'JWJj', 'abc'
```
Different base-64 encoding schemes exist. These are the encoding and decoding rules used by TO_BASE64() and FROM_BASE64():
- The encoding for alphabet value 62 is '+'.
- The encoding for alphabet value 63 is '/'.
- Encoded output consists of groups of 4 printable characters. Each 3 bytes of the input data are encoded using 4 characters. If the last group is incomplete, it is padded with '=' characters to a length of 4.
- A newline is added after each 76 characters of encoded output to divide long output into multiple lines.
- Decoding recognizes and ignores newline, carriage return, tab, and space.
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.
```
mysql> SELECT TRIM('  bar   ');
        -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'
```
This function is multibyte safe.
UCASE(str)
UCASE() is a synonym for UPPER().
UCASE() used within views is rewritten as UPPER().
UNHEX(str)
For a string argument str, UNHEX(str) interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number. The return value is a binary string.
```
mysql> SELECT UNHEX('4D7953514C');
        -> 'MySQL'
mysql> SELECT X'4D7953514C';
        -> 'MySQL'
mysql> SELECT UNHEX(HEX('string'));
        -> 'string'
mysql> SELECT HEX(UNHEX('1267'));
        -> '1267'
```
The characters in the argument string must be legal hexadecimal digits: '0' .. '9', 'A' .. 'F', 'a' .. 'f'. If the argument contains any nonhexadecimal digits, the result is NULL:
```
mysql> SELECT UNHEX('GG');
+-------------+
| UNHEX('GG') |
+-------------+
| NULL        |
+-------------+
```
A NULL result can occur if the argument to UNHEX() is a BINARY column, because values are padded with 0x00 bytes when stored but those bytes are not stripped on retrieval. For example, '41' is stored into a CHAR(3) column as '41 ' and retrieved as '41' (with the trailing pad space stripped), so UNHEX() for the column value returns 'A'. By contrast '41' is stored into a BINARY(3) column as '41\0' and retrieved as '41\0' (with the trailing pad 0x00 byte not stripped). '\0' is not a legal hexadecimal digit, so UNHEX() for the column value returns NULL.
For a numeric argument N, the inverse of HEX(N) is not performed by UNHEX(). Use CONV(HEX(N),16,10) instead. See the description of HEX().
UPPER(str)
Returns the string str with all characters changed to uppercase according to the current character set mapping. The default is utf8mb4.
```
mysql> SELECT UPPER('Hej');
        -> 'HEJ'
```
See the description of LOWER() for information that also applies to UPPER(). This included information about how to perform lettercase conversion of binary strings (BINARY, VARBINARY, BLOB) for which these functions are ineffective, and information about case folding for Unicode character sets.
This function is multibyte safe.
UCASE() used within views is rewritten as UPPER().
WEIGHT_STRING(str [AS {CHAR|BINARY}(N)] [flags])
This function returns the weight string for the input string. The return value is a binary string that represents the comparison and sorting value of the string. It has these properties:
- If WEIGHT_STRING(str1) = WEIGHT_STRING(str2), then str1 = str2 (str1 and str2 are considered equal)
- If WEIGHT_STRING(str1) < WEIGHT_STRING(str2), then str1 < str2 (str1 sorts before str2)
WEIGHT_STRING() is a debugging function intended for internal use. Its behavior can change without notice between MySQL versions. It can be used for testing and debugging of collations, especially if you are adding a new collation. See Section 10.13, “Adding a Collation to a Character Set”.
This list briefly summarizes the arguments. More details are given in the discussion following the list.
- str: The input string expression.
- AS clause: Optional; cast the input string to a given type and length.
- flags: Optional; unused.
The input string, str, is a string expression. If the input is a nonbinary (character) string such as a CHAR, VARCHAR, or TEXT value, the return value contains the collation weights for the string. If the input is a binary (byte) string such as a BINARY, VARBINARY, or BLOB value, the return value is the same as the input (the weight for each byte in a binary string is the byte value). If the input is NULL, WEIGHT_STRING() returns NULL.
Examples:
```
mysql> SET @s = _utf8mb4 'AB' COLLATE utf8mb4_0900_ai_ci;
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| AB   | 4142    | 1C471C60               |
+------+---------+------------------------+
```
```
mysql> SET @s = _utf8mb4 'ab' COLLATE utf8mb4_0900_ai_ci;
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| ab   | 6162    | 1C471C60               |
+------+---------+------------------------+
```
```
mysql> SET @s = CAST('AB' AS BINARY);
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| AB   | 4142    | 4142                   |
+------+---------+------------------------+
```
```
mysql> SET @s = CAST('ab' AS BINARY);
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| ab   | 6162    | 6162                   |
+------+---------+------------------------+
```
The preceding examples use HEX() to display the WEIGHT_STRING() result. Because the result is a binary value, HEX() can be especially useful when the result contains nonprinting values, to display it in printable form:
```
mysql> SET @s = CONVERT(X'C39F' USING utf8) COLLATE utf8_czech_ci;
mysql> SELECT HEX(WEIGHT_STRING(@s));
+------------------------+
| HEX(WEIGHT_STRING(@s)) |
+------------------------+
| 0FEA0FEA               |
+------------------------+
```
For non-NULL return values, the data type of the value is VARBINARY if its length is within the maximum length for VARBINARY, otherwise the data type is BLOB.
The AS clause may be given to cast the input string to a nonbinary or binary string and to force it to a given length:
- AS CHAR(N) casts the string to a nonbinary string and pads it on the right with spaces to a length of N characters. N must be at least 1. If N is less than the length of the input string, the string is truncated to N characters. No warning occurs for truncation.
- AS BINARY(N) is similar but casts the string to a binary string, N is measured in bytes (not characters), and padding uses 0x00 bytes (not spaces).
```
mysql> SET NAMES 'latin1';
mysql> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));
+-------------------------------------+
| HEX(WEIGHT_STRING('ab' AS CHAR(4))) |
+-------------------------------------+
| 41422020                            |
+-------------------------------------+
mysql> SET NAMES 'utf8';
mysql> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));
+-------------------------------------+
| HEX(WEIGHT_STRING('ab' AS CHAR(4))) |
+-------------------------------------+
| 0041004200200020                    |
+-------------------------------------+
```
```
mysql> SELECT HEX(WEIGHT_STRING('ab' AS BINARY(4)));
+---------------------------------------+
| HEX(WEIGHT_STRING('ab' AS BINARY(4))) |
+---------------------------------------+
| 61620000                              |
+---------------------------------------+
```
The flags clause currently is unused.

12.5.1 String Comparison Functions

Table 12.8 String Comparison Operators

Name	Description
`LIKE`	Simple pattern matching
`NOT LIKE`	Negation of simple pattern matching
`STRCMP()`	Compare two strings

If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This affects only comparisons.

Normally, if any expression in a string comparison is case sensitive, the comparison is performed in case-sensitive fashion.

expr LIKE pat [ESCAPE 'escape_char']
Pattern matching using an SQL pattern. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.
The pattern need not be a literal string. For example, it can be specified as a string expression or table column.
Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:
```
mysql> SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
+-----------------------------------------+
| 'ä' LIKE 'ae' COLLATE latin1_german2_ci |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
mysql> SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
+--------------------------------------+
| 'ä' = 'ae' COLLATE latin1_german2_ci |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
```
In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator:
```
mysql> SELECT 'a' = 'a ', 'a' LIKE 'a ';
+------------+---------------+
| 'a' = 'a ' | 'a' LIKE 'a ' |
+------------+---------------+
|          1 |             0 |
+------------+---------------+
1 row in set (0.00 sec)
```
With LIKE you can use the following two wildcard characters in the pattern:
- % matches any number of characters, even zero characters.
- _ matches exactly one character.
```
mysql> SELECT 'David!' LIKE 'David_';
        -> 1
mysql> SELECT 'David!' LIKE '%D%v%';
        -> 1
```
To test for literal instances of a wildcard character, precede it by the escape character. If you do not specify the ESCAPE character, \ is assumed.
- \% matches one % character.
- \_ matches one _ character.
```
mysql> SELECT 'David!' LIKE 'David\_';
        -> 0
mysql> SELECT 'David_' LIKE 'David\_';
        -> 1
```
To specify a different escape character, use the ESCAPE clause:
```
mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1
```
The escape sequence should be empty or one character long. The expression must evaluate as a constant at execution time. If the NO_BACKSLASH_ESCAPES SQL mode is enabled, the sequence cannot be empty.
The following two statements illustrate that string comparisons are not case-sensitive unless one of the operands is case-sensitive (uses a case-sensitive collation or is a binary string):
```
mysql> SELECT 'abc' LIKE 'ABC';
        -> 1
mysql> SELECT 'abc' LIKE _utf8mb4 'ABC' COLLATE utf8mb4_0900_as_cs;
        -> 0
mysql> SELECT 'abc' LIKE _utf8mb4 'ABC' COLLATE utf8mb4_bin;
        -> 0
mysql> SELECT 'abc' LIKE BINARY 'ABC';
        -> 0
```
As an extension to standard SQL, MySQL permits LIKE on numeric expressions.
```
mysql> SELECT 10 LIKE '1%';
        -> 1
```
Note

Because MySQL uses C escape syntax in strings (for example, \n to represent a newline character), you must double any \ that you use in LIKE strings. For example, to search for \n, specify it as \\n. To search for \, specify it as \\\\; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.
Exception: At the end of the pattern string, backslash can be specified as \\. At the end of the string, backslash stands for itself because there is nothing following to escape. Suppose that a table contains the following values:
```
mysql> SELECT filename FROM t1;
+--------------+
| filename     |
+--------------+
| C:           |
| C:\          |
| C:\Programs  |
| C:\Programs\ |
+--------------+
```
To test for values that end with backslash, you can match the values using either of the following patterns:
```
mysql> SELECT filename, filename LIKE '%\\' FROM t1;
+--------------+---------------------+
| filename     | filename LIKE '%\\' |
+--------------+---------------------+
| C:           |                   0 |
| C:\          |                   1 |
| C:\Programs  |                   0 |
| C:\Programs\ |                   1 |
+--------------+---------------------+

mysql> SELECT filename, filename LIKE '%\\\\' FROM t1;
+--------------+-----------------------+
| filename     | filename LIKE '%\\\\' |
+--------------+-----------------------+
| C:           |                     0 |
| C:\          |                     1 |
| C:\Programs  |                     0 |
| C:\Programs\ |                     1 |
+--------------+-----------------------+
```
expr NOT LIKE pat [ESCAPE 'escape_char']
This is the same as NOT (expr LIKE pat [ESCAPE 'escape_char']).
Note

Aggregate queries involving NOT LIKE comparisons with columns containing NULL may yield unexpected results. For example, consider the following table and data:
```
CREATE TABLE foo (bar VARCHAR(10));

INSERT INTO foo VALUES (NULL), (NULL);
```
The query SELECT COUNT(*) FROM foo WHERE bar LIKE '%baz%'; returns 0. You might assume that SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%'; would return 2. However, this is not the case: The second query returns 0. This is because NULL NOT LIKE expr always returns NULL, regardless of the value of expr. The same is true for aggregate queries involving NULL and comparisons using NOT RLIKE or NOT REGEXP. In such cases, you must test explicitly for NOT NULL using OR (and not AND), as shown here:
```
SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%' OR bar IS NULL;
```

STRCMP(expr1,expr2)

STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.

mysql> SELECT STRCMP('text', 'text2');
        -> -1
mysql> SELECT STRCMP('text2', 'text');
        -> 1
mysql> SELECT STRCMP('text', 'text');
        -> 0

STRCMP() performs the comparison using the collation of the arguments.

mysql> SET @s1 = _utf8mb4 'x' COLLATE utf8mb4_0900_ai_ci;
mysql> SET @s2 = _utf8mb4 'X' COLLATE utf8mb4_0900_ai_ci;
mysql> SET @s3 = _utf8mb4 'x' COLLATE utf8mb4_0900_as_cs;
mysql> SET @s4 = _utf8mb4 'X' COLLATE utf8mb4_0900_as_cs;
mysql> SELECT STRCMP(@s1, @s2), STRCMP(@s3, @s4);
+------------------+------------------+
| STRCMP(@s1, @s2) | STRCMP(@s3, @s4) |
+------------------+------------------+
|                0 |               -1 |
+------------------+------------------+

If the collations are incompatible, one of the arguments must be converted to be compatible with the other. See Section 10.8.4, “Collation Coercibility in Expressions”.

mysql> SET @s1 = _utf8mb4 'x' COLLATE utf8mb4_0900_ai_ci;
mysql> SET @s2 = _utf8mb4 'X' COLLATE utf8mb4_0900_ai_ci;
mysql> SET @s3 = _utf8mb4 'x' COLLATE utf8mb4_0900_as_cs;
mysql> SET @s4 = _utf8mb4 'X' COLLATE utf8mb4_0900_as_cs;
-->
mysql> SELECT STRCMP(@s1, @s3);
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT)
and (utf8mb4_0900_as_cs,IMPLICIT) for operation 'strcmp'
mysql> SELECT STRCMP(@s1, @s3 COLLATE utf8mb4_0900_ai_ci);
+---------------------------------------------+
| STRCMP(@s1, @s3 COLLATE utf8mb4_0900_ai_ci) |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+

12.5.2 Regular Expressions

Table 12.9 Regular Expression Functions and Operators

Name	Description
`NOT REGEXP`	Negation of REGEXP
`REGEXP`	Whether string matches regular expression
`REGEXP_INSTR()`	Starting index of substring matching regular expression
`REGEXP_LIKE()`	Whether string matches regular expression
`REGEXP_REPLACE()`	Replace substrings matching regular expression
`REGEXP_SUBSTR()`	Return substring matching regular expression
`RLIKE`	Whether string matches regular expression

A regular expression is a powerful way of specifying a pattern for a complex search. This section discusses the functions and operators available for regular expression matching and illustrates, with examples, some of the special characters and constructs that can be used for regular expression operations. See also Section 3.3.4.7, “Pattern Matching”.

MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. (Prior to MySQL 8.0.4, MySQL used Henry Spencer's implementation of regular expressions, which operates in byte-wise fashion and is not multibyte safe. For information about ways in which applications that use regular expressions may be affected by the implementation change, see Regular Expression Compatibility Considerations.)

Regular Expression Functions and Operators

expr NOT REGEXP pat, expr NOT RLIKE pat
This is the same as NOT (expr REGEXP pat).

expr REGEXP pat, expr RLIKE pat

Returns 1 if the string expr matches the regular expression specified by the pattern pat, 0 otherwise. If expr or pat is NULL, the return value is NULL.

REGEXP and RLIKE are synonyms for REGEXP_LIKE().

For additional information about how matching occurs, see the description for REGEXP_LIKE().

mysql> SELECT 'Michael!' REGEXP '.*';
+------------------------+
| 'Michael!' REGEXP '.*' |
+------------------------+
|                      1 |
+------------------------+
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
+---------------------------------------+
| 'new*\n*line' REGEXP 'new\\*.\\*line' |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
mysql> SELECT 'a' REGEXP '^[a-d]';
+---------------------+
| 'a' REGEXP '^[a-d]' |
+---------------------+
|                   1 |
+---------------------+
mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
+----------------+-----------------------+
| 'a' REGEXP 'A' | 'a' REGEXP BINARY 'A' |
+----------------+-----------------------+
|              1 |                     0 |
+----------------+-----------------------+

REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])
Returns the starting index of the substring of the string expr that matches the regular expression specified by the pattern pat, 0 if there is no match. If expr or pat is NULL, the return value is NULL. Character indexes begin at 1.
REGEXP_INSTR() takes these optional arguments:
- pos: The position in expr at which to start the search. If omitted, the default is 1.
- occurrence: Which occurrence of a match to search for. If omitted, the default is 1.
- return_option: Which type of position to return. If this value is 0, REGEXP_INSTR() returns the position of the matched substring's first character. If this value is 1, REGEXP_INSTR() returns the position following the matched substring. If omitted, the default is 0.
- match_type: A string that specifies how to perform matching. The meaning is as described for REGEXP_LIKE().
For additional information about how matching occurs, see the description for REGEXP_LIKE().
```
mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog');
+------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog') |
+------------------------------------+
|                                  1 |
+------------------------------------+
mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);
+---------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog', 2) |
+---------------------------------------+
|                                     9 |
+---------------------------------------+
mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{2}') |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{4}') |
+-------------------------------------+
|                                   8 |
+-------------------------------------+
```
REGEXP_LIKE(expr, pat[, match_type])
Returns 1 if the string expr matches the regular expression specified by the pattern pat, 0 otherwise. If expr or pat is NULL, the return value is NULL.
The pattern can be an extended regular expression, the syntax for which is discussed in Regular Expression Syntax. The pattern need not be a literal string. For example, it can be specified as a string expression or table column.
The optional match_type argument is a string that may contain any or all the following characters specifying how to perform matching:
- c: Case sensitive matching.
- i: Case insensitive matching.
- m: Multiple-line mode. Recognize line terminators within the string. The default behavior is to match line terminators only at the start and end of the string expression.
- n: The . character matches line terminators. The default is for . matching to stop at the end of a line.
- u: Unix-only line endings. Only the newline character is recognized as a line ending by the ., ^, and $ match operators.
If characters specifying contradictory options are specified within match_type, the rightmost one takes precedence.
By default, regular expression operations use the character set and collation of the expr and pat arguments when deciding the type of a character and performing the comparison. If the arguments have different character sets or collations, coercibility rules apply as described in Section 10.8.4, “Collation Coercibility in Expressions”. Arguments may be specified with explicit collation indicators to change comparison behavior.
```
mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE');
+---------------------------------------+
| REGEXP_LIKE('CamelCase', 'CAMELCASE') |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs);
+------------------------------------------------------------------+
| REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs) |
+------------------------------------------------------------------+
|                                                                0 |
+------------------------------------------------------------------+
```
match_type may be specified with the c or i characters to override the default case sensitivity. Exception: If either argument is a binary string, the arguments are handled in case-sensitive fashion as binary strings, even if match_type contains the i character.

Note

Because MySQL uses the C escape syntax in strings (for example, \n to represent the newline character), you must double any \ that you use in your expr and pat arguments.
```
mysql> SELECT REGEXP_LIKE('Michael!', '.*');
+-------------------------------+
| REGEXP_LIKE('Michael!', '.*') |
+-------------------------------+
|                             1 |
+-------------------------------+
mysql> SELECT REGEXP_LIKE('new*\n*line', 'new\\*.\\*line');
+----------------------------------------------+
| REGEXP_LIKE('new*\n*line', 'new\\*.\\*line') |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
mysql> SELECT REGEXP_LIKE('a', '^[a-d]');
+----------------------------+
| REGEXP_LIKE('a', '^[a-d]') |
+----------------------------+
|                          1 |
+----------------------------+
mysql> SELECT REGEXP_LIKE('a', 'A'), REGEXP_LIKE('a', BINARY 'A');
+-----------------------+------------------------------+
| REGEXP_LIKE('a', 'A') | REGEXP_LIKE('a', BINARY 'A') |
+-----------------------+------------------------------+
|                     1 |                            0 |
+-----------------------+------------------------------+
```
```
mysql> SELECT REGEXP_LIKE('abc', 'ABC');
+---------------------------+
| REGEXP_LIKE('abc', 'ABC') |
+---------------------------+
|                         1 |
+---------------------------+
mysql> SELECT REGEXP_LIKE('abc', 'ABC', 'c');
+--------------------------------+
| REGEXP_LIKE('abc', 'ABC', 'c') |
+--------------------------------+
|                              0 |
+--------------------------------+
```
REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])
Replaces occurrences in the string expr that match the regular expression specified by the pattern pat with the replacement string repl, and returns the resulting string. If expr, pat, or repl is NULL, the return value is NULL.
REGEXP_REPLACE() takes these optional arguments:
- pos: The position in expr at which to start the search. If omitted, the default is 1.
- occurrence: Which occurrence of a match to replace. If omitted, the default is 0 (which means “replace all occurrences”).
- match_type: A string that specifies how to perform matching. The meaning is as described for REGEXP_LIKE().
For additional information about how matching occurs, see the description for REGEXP_LIKE().
```
mysql> SELECT REGEXP_REPLACE('a b c', 'b', 'X');
+-----------------------------------+
| REGEXP_REPLACE('a b c', 'b', 'X') |
+-----------------------------------+
| a X c                             |
+-----------------------------------+
mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
+----------------------------------------------------+
| REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3) |
+----------------------------------------------------+
| abc def X                                          |
+----------------------------------------------------+
```