Chapter 12. Functions and Operators

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, functions, and operators. This chapter describes the functions and operators that are allowed for writing expressions in MySQL.

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. 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. See Section 5.3.2, “The Server SQL Mode”.

For the sake of brevity, most examples in this chapter display the output from the mysql program in abbreviated form. Instead of 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. Operators

12.1.1. Operator Precedence
12.1.2. Parentheses
12.1.3. Comparison Functions and Operators
12.1.4. Logical Operators

12.1.1. Operator Precedence

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

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

The precedence shown for NOT is as of MySQL 5.0.2. For earlier versions, or from 5.0.2 on if the HIGH_NOT_PRECEDENCE SQL mode is enabled, the precedence of NOT is the same as that of the ! operator. See Section 5.3.2, “The Server SQL Mode”.

12.1.2. Parentheses

( ... )
Use parentheses to force the order of evaluation in an expression. For example:
```
mysql> SELECT 1+2*3;
        -> 7
mysql> SELECT (1+2)*3;
        -> 9
```

12.1.3. Comparison Functions and Operators

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.

Some of the functions in this section (such as LEAST() and GREATEST()) return values other than 1 (TRUE), 0 (FALSE), or NULL. However, the value they return is based on comparison operations performed as described by the following rules.

MySQL compares values using the following rules:

If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator.
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. Note that this is not done for arguments in IN()! To be safe, always use complete datetime/date/time strings when doing comparisons.
In all other cases, the arguments are compared as floating-point (real) numbers.

By default, string comparisons are not case sensitive and use the current character set (cp1252 Latin1 by default, which also works well for English).

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.8, “Cast Functions and Operators”.

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

Note that when you are comparing 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: '1', ' 1', '1a', ...

=

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

<=>
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 isNULL.
```
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL
```

<>, !=

Not equal:

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

<=
Less than or equal:
```
mysql> SELECT 0.1 <= 2;
        -> 1
```
<
Less than:
```
mysql> SELECT 2 < 2;
        -> 0
```
>=
Greater than or equal:
```
mysql> SELECT 2 >= 2;
        -> 1
```
>
Greater than:
```
mysql> SELECT 2 > 2;
        -> 0
```
IS boolean_value, IS NOT 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
mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
        -> 1, 1, 0
```
IS [NOT] boolean_value syntax was added in MySQL 5.0.2.
IS NULL, IS NOT NULL
Tests whether a value is or is not NULL.
```
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
        -> 0, 0, 1
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
        -> 1, 1, 0
```
To be able to work well with ODBC programs, MySQL supports the following extra features when using IS NULL:
- You can find the row that contains the most recent AUTO_INCREMENT value by issuing a statement of the following form immediately after generating the value:
```
SELECT * FROM tbl_name WHERE auto_col IS NULL
```
  This behavior can be disabled by setting SQL_AUTO_IS_NULL=0. See Section 13.5.3, “SET Syntax”.
- 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.
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 at the beginning of this section, but applied to all the three arguments.
```
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
```
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.
```
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'
```
Before MySQL 5.0.13, GREATEST() returns NULL only if all arguments are NULL. As of 5.0.13, it 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 at the beginning of this section, but applied to all the arguments.
```
mysql> SELECT 2 IN (0,3,5,'wefwf');
        -> 0
mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');
        -> 1
```
The number of values in the IN list is only limited by the max_allowed_packet value.
In order 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.8.3, “Subqueries with ANY, IN, and 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
```
A comparison of NULL values using = is always false.
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 the return value is used in an INTEGER context or all arguments are integer-valued, they are compared as integers.
- If the return value is used in a REAL context or all arguments are real-valued, they are compared as reals.
- If any argument is a case-sensitive string, the arguments are compared as case-sensitive strings.
- In all other cases, the arguments are compared as case-insensitive strings.
Before MySQL 5.0.13, LEAST() returns NULL only if all arguments are NULL. As of 5.0.13, it returns NULL if any argument is NULL.
```
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'
```
Note that the preceding conversion rules can produce strange results in some borderline cases:
```
mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);
        -> -9223372036854775808
```
This happens because MySQL reads 9223372036854775808.0 in an integer context. The integer representation is not good enough to hold the value, so it wraps to a signed integer.

12.1.4. Logical Operators

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 non-zero value for TRUE.

NOT, !
Logical NOT. Evaluates to 1 if the operand is 0, to 0 if the operand is non-zero, 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.
Note that the precedence of the NOT operator changed in MySQL 5.0.2. See Section 12.1.1, “Operator Precedence”.

AND, &&

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

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

OR, ||
Logical OR. When both operands are non-NULL, the result is 1 if any operand is non-zero, and 0 otherwise. With a NULL operand, the result is 1 if the other operand is non-zero, and NULL otherwise. If both operands are NULL, the result is NULL.
```
mysql> SELECT 1 || 1;
        -> 1
mysql> SELECT 1 || 0;
        -> 1
mysql> SELECT 0 || 0;
        -> 0
mysql> SELECT 0 || NULL;
        -> NULL
mysql> SELECT 1 || 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 non-zero, 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.2. Control Flow Functions

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 version returns the result where value=compare-value. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.
```
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
```
The default return type of a CASE expression is the compatible aggregated type of all return values, but also depends on the context in which it is used. If used in a string context, the result is returned as a string. If used in a numeric context, then the result is returned as a decimal, real, or integer value.

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

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'

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.

expr1 is evaluated as an integer value, which means that if you are testing floating-point or string values, you should do so using a comparison operation.

mysql> SELECT IF(0.1,1,0);
        -> 0
mysql> SELECT IF(0.1<>0,1,0);
        -> 1

In the first case shown, IF(0.1) returns 0 because 0.1 is converted to an integer value, resulting in a test of IF(0). This may not be what you expect. In the second case, the comparison tests the original floating-point value to see whether it is non-zero. The result of the comparison is used as an integer.

The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows:

Expression	Return Value
`expr2` or `expr3` returns a string	string
`expr2` or `expr3` returns a floating-point value	floating-point
`expr2` or `expr3` returns an integer	integer

If expr2 and expr3 are both strings, the result is case sensitive if either string is case sensitive.

IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.
```
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 result value 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:
```
CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
```
In this example, the type of the test column is CHAR(4).
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.
```
mysql> SELECT NULLIF(1,1);
        -> NULL
mysql> SELECT NULLIF(1,2);
        -> 1
```
Note that MySQL evaluates expr1 twice if the arguments are not equal.

12.3. String Functions

12.3.1. String Comparison Functions

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 7.5.2, “Tuning Server Parameters”.

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

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 characters with numeric values from 0 to 255.
```
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])

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'

As of MySQL 5.0.15, 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             |
+------------------+--------------------+

CHAR() returns a binary string. To produce a string in a given character set, the optional USING clause may be used:

mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));
+---------------------+--------------------------------+
| CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 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.

Before MySQL 5.0.15, CHAR() returns a string in the connection character set and the USING clause is unavailable. In addition, each argument is interpreted modulo 256, so CHAR(256) and CHAR(256*256) both are equivalent to CHAR(0).

CHAR_LENGTH(str)
Returns the length of the string str, measured in characters. A multi-byte character counts as a single character. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.
CHARACTER_LENGTH(str)
CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().
COMPRESS(string_to_compress)
Compresses a string. This function requires MySQL to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL. The compressed string can be uncompressed with UNCOMPRESS().
```
mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));
        -> 21
mysql> SELECT LENGTH(COMPRESS(''));
        -> 0
mysql> SELECT LENGTH(COMPRESS('a'));
        -> 13
mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16)));
        -> 15
```
The compressed string contents are stored the following way:
- Empty strings are stored as empty strings.
- Non-empty strings are stored as a four-byte length of the uncompressed string (low byte first), followed by the compressed string. If the string ends with space, an extra ‘.’ character is added to avoid problems with endspace trimming should the result be stored in a CHAR or VARCHAR column. (Use of CHAR or VARCHAR to store compressed strings is not recommended. It is better to use a BLOB column instead.)
CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. May have one or more arguments. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that you can use explicit type cast, like in this example: SELECT CONCAT(CAST(int_col AS CHAR), char_col)
```
mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'
```
CONCAT_WS(separator,str1,str2,...)
CONCAT_WS() stands for CONCAT 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. The function skips any NULL values after the separator argument.
```
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 NULLs.)
CONV(N,from_base,to_base)
Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision.
```
mysql> SELECT CONV('a',16,2);
        -> '1010'
mysql> SELECT CONV('6E',18,8);
        -> '172'
mysql> SELECT CONV(-17,10,-18);
        -> '-H'
mysql> SELECT CONV(10+'10'+'10'+0xa,10,10);
        -> '40'
```
ELT(N,str1,str2,str3,...)
Returns 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, 'ej', 'Heja', 'hej', 'foo');
        -> 'ej'
mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
        -> 'foo'
```
EXPORT_SET(bits,on,off[,separator[,number_of_bits]])
Returns a string in which for every bit set in the value bits, you get an on string and for every reset bit 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 (defaults to 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 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('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 2
mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 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)
Formats the number X to a format like '#,###,###.##', rounded to D decimals, and returns the result as a string. See For details, see Section 12.9.4, “Miscellaneous Functions”.
HEX(N_or_S)
If N_OR_S is a number, returns a string representation of the hexadecimal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,16).
If N_OR_S is a string, returns a hexadecimal string representation of N_OR_S where each character in N_OR_S is converted to two hexadecimal digits.
```
mysql> SELECT HEX(255);
        -> 'FF'
mysql> SELECT 0x616263;
        -> 'abc'
mysql> SELECT HEX('abc');
        -> 616263
```
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 is 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 multi-byte 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 multi-byte safe, and is case sensitive only if at least one argument is a binary string.
LCASE(str)
LCASE() is a synonym for LOWER().
LEFT(str,len)
Returns the leftmost len characters from the string str.
```
mysql> SELECT LEFT('foobarbar', 5);
        -> 'fooba'
```
LENGTH(str)
Returns the length of the string str, measured in bytes. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.
```
mysql> SELECT LENGTH('text');
        -> 4
```
LOAD_FILE(file_name)
Reads the file and returns the file contents as a string. The file must be located on the server, you must specify the full pathname 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 file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.
```
mysql> UPDATE tbl_name
           SET blob_column=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.
```
mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
mysql> SELECT LOCATE('bar', 'foobarbar',5);
        -> 7
```
This function is multi-byte 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 cp1252 Latin1).
```
mysql> SELECT LOWER('QUADRATICALLY');
        -> 'quadratically'
```
This function is multi-byte safe.
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 multi-byte 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 multi-byte 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²) ...
```
If the leftmost character is not a multi-byte 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 surrounded by single quotes and with each instance of single quote (‘'’), backslash (‘\’), ASCII NUL, and Control-Z preceded by a backslash. If the argument is NULL, the return value is the word “NULL” without surrounding single quotes.
```
mysql> SELECT QUOTE('Don\'t!');
        -> 'Don\'t!'
mysql> SELECT QUOTE(NULL);
        -> NULL
```
REPEAT(str,count)
Returns a string consisting of the string str repeated count times. If count <= 0, 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 multi-byte safe.
REVERSE(str)
Returns the string str with the order of the characters reversed.
```
mysql> SELECT REVERSE('abc');
        -> 'cba'
```
This function is multi-byte safe.
RIGHT(str,len)
Returns the rightmost len characters from the string str.
```
mysql> SELECT RIGHT('foobarbar', 4);
        -> 'rbar'
```
This function is multi-byte 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 multi-byte safe.
RTRIM(str)
Returns the string str with trailing space characters removed.
```
mysql> SELECT RTRIM('barbar   ');
        -> 'barbar'
```
This function is multi-byte 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 non-alphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.
```
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 then duplicates, whereas the enhanced version discards duplicates first and then vowels.
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);
        -> '      '
```
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.
```
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 multi-byte safe.
Note that if you use a value less than than 1 for len, the result is always an empty string.
SUBSTR() is a synonym for SUBSTRING().
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 multi-byte safe.
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
Returns the string str with all remstr prefixes and/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 multi-byte safe.
UCASE(str)
UCASE() is a synonym for UPPER().
UNCOMPRESS(string_to_uncompress)
Uncompresses a string compressed by the COMPRESS() function. If the argument is not a compressed value, the result is NULL. This function requires MySQL to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL.
```
mysql> SELECT UNCOMPRESS(COMPRESS('any string'));
        -> 'any string'
mysql> SELECT UNCOMPRESS('any string');
        -> NULL
```
UNCOMPRESSED_LENGTH(compressed_string)
Returns the length of a compressed string before compression.
```
mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));
        -> 30
```
UNHEX(str)
Performs the opposite operation from HEX(str). That is, it interprets each pair of hexadecimal digits in the argument as a number and converts it to the character represented by the number. The resulting characters are returned as a binary string.
```
mysql> SELECT UNHEX('4D7953514C');
        -> 'MySQL'
mysql> SELECT 0x4D7953514C;
        -> 'MySQL'
mysql> SELECT UNHEX(HEX('string'));
        -> 'string'
mysql> SELECT HEX(UNHEX('1267'));
        -> '1267'
```
UPPER(str)
Returns the string str with all characters changed to uppercase according to the current character set mapping (the default is cp1252 Latin1).
```
mysql> SELECT UPPER('Hej');
        -> 'HEJ'
```
This function is multi-byte safe.

12.3.1. String Comparison Functions

MySQL automatically converts numbers to strings as necessary, and vice versa.

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

If you want to convert a number to a string explicitly, use the CAST() or CONCAT() function:

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

CAST() is preferable.

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 SQL simple regular expression comparison. 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 have different results than 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 |
+--------------------------------------+
```
With LIKE you can use the following two wildcard characters in the pattern:
Character Description
% 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 the character with the escape character. If you do not specify the ESCAPE character, ‘\’ is assumed.
String Description
\% 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. As of MySQL 5.0.16, 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 a binary string:
```
mysql> SELECT 'abc' LIKE 'ABC';
        -> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
        -> 0
```
In MySQL, LIKE is allowed on numeric expressions. (This is an extension to the standard SQL LIKE.)
```
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.
expr NOT LIKE pat [ESCAPE 'escape-char']
This is the same as NOT (expr LIKE pat [ESCAPE 'escape-char']).
expr NOT REGEXP pat expr NOT RLIKE pat
This is the same as NOT (expr REGEXP pat).
expr REGEXP pat expr RLIKE pat
Performs a pattern match of a string expression expr against a pattern pat. The pattern can be an extended regular expression. The syntax for regular expressions is discussed in Appendix G, MySQL Regular Expressions. Returns 1 if expr matches pat; otherwise it returns 0. If either expr or pat is NULL, the result is NULL. RLIKE is a synonym for REGEXP, provided for mSQL compatibility.
The pattern need not be a literal string. For example, it can be specified as a string expression or table column.
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 REGEXP strings.
REGEXP is not case sensitive, except when used with binary strings.
```
mysql> SELECT 'Monty!' REGEXP 'm%y%%';
        -> 0
mysql> SELECT 'Monty!' REGEXP '.*';
        -> 1
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
        -> 1
mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
        -> 1  0
mysql> SELECT 'a' REGEXP '^[a-d]';
        -> 1
```
REGEXP and RLIKE use the current character set (cp1252 Latin1 by default) when deciding the type of a character. Warning: These operators are not multi-byte safe.
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() uses the current character set when performing comparisons. This makes the default comparison behavior case insensitive unless one or both of the operands are binary strings.

12.4. Numeric Functions

12.4.1. Arithmetic Operators
12.4.2. Mathematical Functions

12.4.1. Arithmetic Operators

The usual arithmetic operators are available. Note that in the case of -, +, and *, the result is calculated with BIGINT (64-bit) precision if both arguments are integers. If one of the arguments is an unsigned integer, and the other argument is also an integer, the result is an unsigned integer. See Section 12.8, “Cast Functions and Operators”.

+
Addition:
```
mysql> SELECT 3+5;
        -> 8
```
-
Subtraction:
```
mysql> SELECT 3-5;
        -> -2
```
-
Unary minus. Changes the sign of the argument.
```
mysql> SELECT - 2;
        -> -2
```
Note: If this operator is used with a BIGINT, the return value is also a BIGINT. This means that you should avoid using – on integers that may have the value of –2⁶³.

*

Multiplication:

mysql> SELECT 3*5;
        -> 15
mysql> SELECT 18014398509481984*18014398509481984.0;
        -> 324518553658426726783156020576256.0
mysql> SELECT 18014398509481984*18014398509481984;
        -> 0

The result of the last expression is incorrect because the result of the integer multiplication exceeds the 64-bit range of BIGINT calculations. (See Section 11.2, “Numeric Types”.)

/
Division:
```
mysql> SELECT 3/5;
        -> 0.60
```
Division by zero produces a NULL result:
```
mysql> SELECT 102/(1-1);
        -> NULL
```
A division is calculated with BIGINT arithmetic only if performed in a context where its result is converted to an integer.
DIV
Integer division. Similar to FLOOR() but safe with BIGINT values.
```
mysql> SELECT 5 DIV 2;
        -> 2
```

12.4.2. Mathematical Functions

All mathematical functions return NULL in the event of an error.

ABS(X)

Returns the absolute value of X.

mysql> SELECT ABS(2);
        -> 2
mysql> SELECT ABS(-32);
        -> 32

This function is safe to use with BIGINT values.

ACOS(X)

Returns the arc cosine of X, that is, the value whose cosine is X. Returns NULL if X is not in the range -1 to 1.

mysql> SELECT ACOS(1);
        -> 0
mysql> SELECT ACOS(1.0001);
        -> NULL
mysql> SELECT ACOS(0);
        -> 1.5707963267949

ASIN(X)

Returns the arc sine of X, that is, the value whose sine is X. Returns NULL if X is not in the range -1 to 1.

mysql> SELECT ASIN(0.2);
        -> 0.20135792079033
mysql> SELECT ASIN('foo');

+-------------+
| ASIN('foo') |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
+---------+------+-----------------------------------------+

ATAN(X)

Returns the arc tangent of X, that is, the value whose tangent is X.

mysql> SELECT ATAN(2);
        -> 1.1071487177941
mysql> SELECT ATAN(-2);
        -> -1.1071487177941

ATAN(Y,X) , ATAN2(Y,X)
Returns the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result.
```
mysql> SELECT ATAN(-2,2);
        -> -0.78539816339745
mysql> SELECT ATAN2(PI(),0);
        -> 1.5707963267949
```
CEILING(X) CEIL(X)
Returns the smallest integer value not less than X.
```
mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEIL(-1.23);
        -> -1
```
These two functions are synonymous. Note that the return value is converted to a BIGINT.
COS(X)
Returns the cosine of X, where X is given in radians.
```
mysql> SELECT COS(PI());
        -> -1
```

COT(X)

Returns the cotangent of X.

mysql> SELECT COT(12);
        -> -1.5726734063977
mysql> SELECT COT(0);
        -> NULL

CRC32(expr)
Computes a cyclic redundancy check value and returns a 32-bit unsigned value. The result is NULL if the argument is NULL. The argument is expected to be a string and (if possible) is treated as one if it is not.
```
mysql> SELECT CRC32('MySQL');
        -> 3259397556
mysql> SELECT CRC32('mysql');
        -> 2501908538
```

DEGREES(X)

Returns the argument X, converted from radians to degrees.

mysql> SELECT DEGREES(PI());
        -> 180
mysql> SELECT DEGREES(PI() / 2);
        -> 90

EXP(X)

Returns the value of e (the base of natural logarithms) raised to the power of X.

mysql> SELECT EXP(2);
        -> 7.3890560989307
mysql> SELECT EXP(-2);

Character	Description
`%`	Matches any number of characters, even zero characters
`_`	Matches exactly one character

String	Description
`\%`	Matches one ‘`%`’ character
`\_`	Matches one ‘`_`’ character