Functions are called to perform a calculation and return a value. By default, functions must be invoked with no space between the function name and the parentheses following it:

NOW() Correct NOW () Incorrect

If you start the server in ANSI mode with the `--ansi` option, it will allow spaces after function names, although a side-effect is that all function names become reserved words. You may also be able to select this behavior on a connection-specific basis, depending on the client program. For example, you can start `mysql` with the `--ignore-space` option; in C programs, you can call `mysql_real_connect()` with the `CLIENT_IGNORE_SPACE` option.

In most cases, multiple arguments to a function are separated by commas. Spaces are allowed around function arguments:

CONCAT('abc','def') This is okay CONCAT( 'abc' , 'def' ) This is okay, too

There are a few exceptions to this syntax, such as `TRIM()` or `EXTRACT()`:

TRIM(' ' FROM ' x ') 'x' EXTRACT(YEAR FROM '2003-01-01') 2003

Each function entry describes its allowable syntax.

`GREATEST(``expr1,expr2``,...)`Returns the largest argument, where "largest" is defined according to the following rules:

If the function is called in an integer context or all its arguments are integers, the arguments are compared as integers.

If the function is called in a floating-point context or all its arguments are floating-point values, the arguments are compared as floating-point values.

If neither of the preceding two rules applies, the arguments are compared as strings. The comparisons are not case sensitive unless some argument is a binary string.

GREATEST(2,3,1) 3 GREATEST(38.5,94.2,-1) 94.2 GREATEST('a','ab','abc') 'abc' GREATEST(1,3,5) 5 GREATEST('A','b','C') 'C' GREATEST(BINARY 'A','b','C') 'b'

`GREATEST()`was introduced in MySQL 3.22.5. In earlier versions, you can use`MAX()`instead.

`IF(``expr1,expr2,expr3``)`If

`expr1`is true (not`0`or`NULL`), returns`expr2`; otherwise, it returns`expr3`.`IF()`returns a number or string according to the context in which it is used.IF(1,'true','false') 'true' IF(0,'true','false') 'false' IF(NULL,'true','false') 'false' IF(1.3,'non-zero','zero') 'non-zero' IF(0.3,'non-zero','zero') 'zero' IF(0.3 != 0,'non-zero','zero') 'non-zero'

`expr1`is evaluated as an integer value, and the last three examples indicate how this behavior can catch you unaware if you're not careful.`1.3`converts to the integer value`1`, which is true. But`0.3`converts to the integer value`0`, which is false. The last example shows the proper way to use a floating-point number: Test the number using a comparison expression. The comparison treats the number correctly as a floating-point value and produces a true or false comparison result as an integer`1`or`0`, as required by`IF()`.`IFNULL(``expr1,expr2``)`Returns

`expr2`if the value of the expression`expr1`is`NULL`; otherwise, it returns`expr1`.`IFNULL()`returns a number or string depending on the context in which it is used.IFNULL(NULL,'null') 'null' IFNULL('not null','null') 'not null'

`INTERVAL(``n,n1,n2``,...)`Returns

`0`if`n < n1`,`1`if`n < n2`, and so on or`?1`if`n`is`NULL`. The values`n1, n2``, ...`must be in strictly increasing order (`n1 < n2``< ...`) because a fast binary search is used. Otherwise,`INTERVAL()`will behave unpredictably.INTERVAL(1.1,0,1,2) 2 INTERVAL(7,1,3,5,7,9) 4

`ISNULL(``expr``)`Returns

`1`if the value of the expression`expr`is`NULL`; otherwise, it returns`0`.ISNULL(NULL) 1 ISNULL(0) 0 ISNULL(1) 0

`LEAST(``expr1,expr2``,...)`Returns the smallest argument, where "smallest" is defined using the same comparison rules as for the

`GREATEST()`function.LEAST(2,3,1) 1 LEAST(38.5,94.2,-1) -1.0 LEAST('a','ab','abc') 'a'

`LEAST()`was introduced in MySQL 3.22.5. In earlier versions, you can use`MIN()`instead.`NULLIF(``expr1,expr2``)`Returns

`expr1`if the two expression values differ,`NULL`if they are the same.NULLIF(3,4) 3 NULLIF(3,3) NULL

`NULLIF()`was introduced in MySQL 3.23.19.`STRCMP(``str1,str2``)`This function returns

`1, 0`, or`?1`, depending on whether the first argument is lexically greater than, equal to, or less than the second argument. If either argument is`NULL`, the function returns`NULL`. As of MySQL 4.0.0, the comparison is not case sensitive unless either argument is a binary string:STRCMP('a','a') 0 STRCMP('a','A') 0 STRCMP('A','a') 0 STRCMP(BINARY 'a','A') 1 STRCMP(BINARY 'A','a') -1

Prior to MySQL 4.0.0, the comparison is case sensitive:

STRCMP('a','a') 0 STRCMP('a','A') 1 STRCMP('A','a') -1

These functions convert values from one type to another.

`CAST(``expr``AS``type``)`Cast an expression value

`expr`to a given type. The`type`value can be`BINARY`(binary string),`DATE`,`DATETIME`,`TIME`,`SIGNED`,`SIGNED INTEGER`,`UNSIGNED`, or`UNSIGNED INTEGER`.CAST(304 AS BINARY) '304' CAST(-1 AS UNSIGNED) 18446744073709551615

`CAST()`can be useful for forcing columns to have a particular type when creating a new table with`CREATE TABLE ... SELECT`.mysql> CREATE TABLE t SELECT CAST(20020101 AS DATE) AS date_val; mysql> SHOW COLUMNS FROM t; +----------+------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------+------+-----+------------+-------+ | date_val | date | | | 0000-00-00 | | +----------+------+------+-----+------------+-------+ mysql> SELECT * FROM t; +------------+ | date_val | +------------+ | 2002-01-01 | +------------+

`CAST()`was introduced in MySQL 4.0.2. It is similar to`CONVERT()`, but`CAST()`has ANSI SQL syntax, whereas`CONVERT()`has ODBC syntax.CONVERT(expr,type) CONVERT(expr USING charset)

The first form of

`CONVERT()`serves the same purpose as`CAST()`, but has slightly different syntax. The`expr`and`type`arguments have the same meaning. The`USING`form converts the value to use a given character set.CONVERT(304,BINARY) '304' CONVERT(-1,UNSIGNED) 18446744073709551615 CONVERT('abc' USING utf8); 'abc'

`CONVERT()`was introduced in MySQL 4.0.2. The form that has`USING`syntax was introduced in MySQL 4.1.0.

Numeric functions return `NULL` if an error occurs. For example, if you pass arguments to the function that are out of range or otherwise invalid, the function will return `NULL`.

`ABS(``x``)`Returns the absolute value of

`x`.ABS(13.5) 13.5 ABS(-13.5) 13.5

`ACOS(`x`)`Returns the arccosine of

`x`or`NULL`if`x`is not in the range from`?1`to`1`.ACOS(1) 0.000000 ACOS(0) 1.570796 ACOS(-1) 3.141593

`ASIN(`x`)`Returns the arcsine of

`x`or`NULL`if`x`is not in the range from`?1`to`1`.ASIN(1) 1.570796 ASIN(0) 0.000000 ASIN(-1) -1.570796

ATAN(x) ATAN(y,x)

The one-argument form of

`ATAN()`returns the arctangent of`x`. The two-argument form is a synonym for`ATAN2()`.ATAN(1) 0.785398 ATAN(0) 0.000000 ATAN(-1) -0.785398

`ATAN2(y,x)`This is like

`ATAN(y/x)`, but it uses the signs of both arguments to determine the quadrant of the return value.ATAN2(1,1) 0.785398 ATAN2(1,-1) 2.356194 ATAN2(-1,1) -0.785398 ATAN2(-1,-1) -2.356194

`CEILING(``x``)`Returns the smallest integer not less than

`x`. The return type is always a`BIGINT`value.CEILING(3.8) 4 CEILING(-3.8) -3

`COS(``x``)`Returns the cosine of

`x`, where`x`is measured in radians.COS(0) 1.000000 COS(PI()) -1.000000 COS(PI()/2) 0.000000

`COT(``x``)`Returns the cotangent of

`x`, where`x`is measured in radians.COT(PI()/2) 0.00000000 COT(PI()/4) 1.00000000

`CRC32(``str``)`Computes a cyclic redundancy check value from the argument, which is treated as a string. The return value is a 32-bit unsigned value in the range from 0 to 2

^{32}?1 or`NULL`if the argument is`NULL`.CRC32('xyz') 3951999591 CRC32('0') 4108050209 CRC32(0) 4108050209 CRC32(NULL) NULL

`CRC32()`was introduced in MySQL 4.1.0.`DEGREES(``x``)`Returns the value of

`x`, converted from radians to degrees.DEGREES(PI()) 180 DEGREES(PI()*2) 360 DEGREES(PI()/2) 90 DEGREES(-PI()) -180

`EXP(``x``)`Returns

`e`^{x}, where`e`is the base of natural logarithms.EXP(1) 2.718282 EXP(2) 7.389056 EXP(-1) 0.367879 1/EXP(1) 0.36787944

`FLOOR(``x``)`Returns the largest integer not greater than

`x`. The return type is always a`BIGINT`value.FLOOR(3.8) 3 FLOOR(-3.8) -4

`LN(``x``)`This is a synonym for

`LOG()`; it was introduced in MySQL 4.0.3.LOG(x) LOG(b, x)

The one-argument form of

`LOG()`returns the natural (base`e`) logarithm of`x`.LOG(0) NULL LOG(1) 0.000000 LOG(2) 0.693147 LOG(EXP(1)) 1.000000

The two-argument form returns the logarithm of

`x`to the base`b`.LOG(10,100) 2.000000 LOG(2,256) 8.000000

The two-argument form was introduced in MySQL 4.0.3. For earlier versions, you can compute the logarithm of

`x`to the base`b`using`LOG(``x``)/LOG(``b``)`.LOG(100)/LOG(10) 2.00000000 LOG10(100) 2.000000

`LOG10(``x``)`Returns the logarithm of

`x`to the base 10.LOG10(0) NULL LOG10(10) 1.000000 LOG10(100) 2.000000

`LOG2(``x``)`Returns the logarithm of

`x`to the base 2.LOG2(0) NULL LOG2(255) 7.994353 LOG2(32767) 14.999956

`LOG2()`tells you the "width" of a value in bits. One use for this is to assess the amount of storage required for the value.`LOG2()`was introduced in MySQL 4.0.3.`MOD(``m,n``)``MOD()`performs a modulo operation.`MOD(``m,n``)`is the same as`m %n`. See the "Arithmetic Operators" section earlier in this appendix.`PI()`Returns the value of p.

PI() 3.141593

`POW(``x,y``)`Returns

`x`^{y}; that is,`x`raised to the power`y`.POW(2,3) 8.000000 POW(2,-3) 0.125000 POW(4,.5) 2.000000 POW(16,.25) 2.000000

`POWER(``x,y``)`This function is a synonym for

`POW()`.`RADIANS(``x``)`Returns the value of

`x`, converted from degrees to radians.RADIANS(0) 0 RADIANS(360) 6.2831853071796 RADIANS(-360) -6.2831853071796

`RAND() RAND(n)`

`RAND()`returns a random floating-point value in the range from 0.0 to 1.0.`RAND(``n``)`does the same thing, using`n`as the seed value for the randomizer. All calls to`RAND()`with the same value of`n`return the same result. You can use this property when you need a repeatable sequence of numbers. (Call`RAND()`the first time with an argument of`n`, and then call it successively with no argument to get the next numbers in the sequence.)RAND(10) 0.18109053110805 RAND(10) 0.18109053110805 RAND() 0.7502322306393 RAND() 0.20788959060599 RAND(10) 0.18109053110805 RAND() 0.7502322306393 RAND() 0.20788959060599

In the examples, notice how sequential calls to

`RAND()`behave when you supply an argument compared to when you do not.Seeding operations are client-specific. If one client invokes

`RAND(``n``)`to seed the random number generator, that does not affect the numbers returned for other clients.ROUND(x) ROUND(x,d)

`ROUND(``x``)`returns the value of`x`, rounded to an integer.`ROUND(``x,d``)`returns the value of`x`, rounded to a number with`d`decimal places. If`d`is`0`, the result has no decimal point or fractional part.ROUND(15.3) 15 ROUND(15.5) 16 ROUND(-33.27834,2) -33.28 ROUND(1,4) 1.0000

The precise behavior of

`ROUND()`depends on the rounding behavior of your underlying math library. This means the results from`ROUND()`may vary from system to system.`SIGN(``x``)`Returns

`?1`,`0`, or`1`, depending on whether the value of`x`is negative, zero, or positive.SIGN(15.803) 1 SIGN(0) 0 SIGN(-99) -1

`SIN(``x``)`Returns the sine of

`x`, where`x`is measured in radians.SIN(0) 0.000000 SIN(PI()) 0.000000 SIN(PI()/2) 1.000000

`SQRT(``x``)`Returns the non-negative square root of

`x`.SQRT(625) 25.000000 SQRT(2.25) 1.500000 SQRT(-1) NULL

`TAN(``x``)`Returns the tangent of

`x`, where`x`is measured in radians.TAN(0) 0.000000 TAN(PI()/4) 1.000000

`TRUNCATE(``x,d``)`Returns the value

`x`, with the fractional part truncated to`d`decimal places. If`d`is`0`, the result has no decimal point or fractional part. If`d`is greater than the number of decimal places in`x`, the fractional part is right-padded with trailing zeros to the desired width.TRUNCATE(1.23,1) 1.2 TRUNCATE(1.23,0) 1 TRUNCATE(1.23,4) 1.2300

Most of the functions in this section return a string result. Some of them, such as `LENGTH()`, take strings as arguments and return a number. For functions that operate on strings based on string positions, the position of the first (leftmost) character is 1 (not 0).

Several string functions are multi-byte safe as of MySQL 3.23.7: `INSERT()`, `INSTR()`, `LCASE()`, `LEFT()`, `LOCATE()`, `LOWER()`, `MID()`,`POSITION()`, `REPLACE()`, `REVERSE()`, `RIGHT()`, `RTRIM()`,`SUBSTRING()`, `SUBSTRING_INDEX()`, `TRIM()`, `UCASE()`, and `UPPER()`.

`ASCII(``str``)`Returns the ASCII code of the leftmost character of the string

`str`. It returns`0`if`str`is empty or`NULL`if`str`is`NULL`.ASCII('abc') 97 ASCII('') 0 ASCII(NULL) NULL

`BIN(``n``)`Returns the value of

`n`in binary form as a string. The following two expressions are equivalent:BIN(n) CONV(n,10,2)

See the description of

`CONV()`for more information.`BIN()`was introduced in MySQL 3.22.4.`CHAR(``n1,n2``,...)`Interprets the arguments as ASCII codes and returns a string consisting of the concatenation of the corresponding character values.

`NULL`arguments are ignored.CHAR(65) 'A' CHAR(97) 'a' CHAR(89,105,107,101,115,33) 'Yikes!'

`CHARACTER_LENGTH(``str``)`This function is a synonym for

`CHAR_LENGTH()`.`CHAR_LENGTH(``str``)`This function is similar to

`LENGTH()`, except that as of MySQL 3.23.6, multi-byte characters are each counted as having a length of 1.`CHARSET(``str``)`Returns the name of the character set in which the string

`str`is represented.CHARSET('abc') 'latin1' CHARSET(CONVERT('abc' USING utf8)) 'utf8'

`CHARSET()`was introduced in MySQL 4.1.0.`COALESCE(``expr1,expr2``,...)`Returns the first non-

`NULL`element in the list or`NULL`if no argument is non-`NULL`.COALESCE(NULL,1/0,2,'a',45+97) '2' COALESCE(NULL,1/0) NULL

`COALESCE()`was introduced in MySQL 3.23.3.`CONCAT(``str1,str2``,...)`Returns a string consisting of the concatenation of all of its arguments. Returns

`NULL`if any argument is`NULL`.`CONCAT()`can be called with a single argument.CONCAT('abc','def') 'abcdef' CONCAT('abc') 'abc' CONCAT('abc',NULL) NULL CONCAT('Hello',', ','goodbye') 'Hello, goodbye'

If the arguments to

`CONCAT()`have different character sets (as is possible as of MySQL 4.1), the result has the character set of the first argument.Another way to concatenate strings is to just specify them next to each other.

'three' 'blind' 'mice' 'threeblindmice' 'abc' 'def' = 'abcdef' 1

`CONCAT_WS(``delim,str1,str2``,...)`Returns a string consisting of the concatenation of its second and following arguments, with the

`delim`string used as the separator between strings. Returns`NULL`if`delim`is`NULL`, but ignores any`NULL`values or empty strings in the list of strings to be concatenated.CONCAT_WS(',','a','b','c','d') 'a,b,c,d' CONCAT_WS('*-*','lemon','','lime',NULL,'grape') 'lemon*-*lime*-*grape'

`CONV(``n,from_base,to_base``)`Given a number

`n`represented in base`from_base`returns a string representation of`n`in base`to_base`. The result is`NULL`if any argument is`NULL`.`from_base`and`to_base`should be integers in the range from 2 to 36.`n`is treated as a`BIGINT`value (64-bit integer), but can be specified as a string because numbers in bases higher than 10 can contain non-decimal digits. (This is also the reason that`CONV()`returns a string; the result may contain characters from`A`to`Z`for bases 11 to 36.) The result is`0`if`n`is not a legal number in base`from_base`. (For example, if`from_base`is`16`and`n`is`'abcdefg'`, the result is`0`because`g`is not a legal hexadecimal digit.)Non-decimal characters in

`n`can be specified in either uppercase or lowercase. Non-decimal characters in the result will be uppercase.Convert 14 specified as a hexadecimal number to binary:

CONV('e',16,2) '1110'

Convert 255 specified in binary to octal:

CONV(11111111,2,8) '377' CONV('11111111',2,8) '377'

`n`is treated as an unsigned number by default. If you specify`to_base`as a negative number,`n`is treated as a signed number.CONV(-10,10,16) 'FFFFFFFFFFFFFFF6' CONV(-10,10,-16) '-A'

`CONV()`was introduced in MySQL 3.22.4.`ELT(``n,str1,str2``,...)`Returns the

`n`-th string from the list of strings`str1,str2``,...`. Returns`NULL`if`n`is`NULL`, the`n`th string is`NULL`, or there is no`n`th string. The index of the first string is 1.`ELT()`is complementary to`FIELD()`.ELT(3,'a','b','c','d','e') 'c' ELT(0,'a','b','c','d','e') NULL ELT(6,'a','b','c','d','e') NULL ELT(FIELD('b','a','b','c'),'a','b','c') 'b'

`EXPORT_SET(``n,on,off``,[``delim``,[``bit_count``]])`Returns a string consisting of the strings

`on`and`off`, separated by the delimiter string`delim. on`is used to represent each bit that is set in`n`, and`off`is used to represent each bit that is not set.`bit_count`indicates the maximum number of bits in`n`to examine. The default delimiter is a comma, and the default`bit_count`value is 64. Returns`NULL`if any argument is`NULL`.EXPORT_SET(7,'+','-','',5) '+++--' EXPORT_SET(0xa,'1','0','',6) '010100' EXPORT_SET(97,'Y','N',',',8) 'Y,N,N,N,N,Y,Y,N'

`EXPORT_SET()`was introduced in MySQL 3.23.2.`FIELD(``str,str1,str2``,...)`Finds

`str`in the list of strings`str1,str2``, ...`and returns the index of the matching string. Returns`0`if there is no match or if`str`is`NULL`. The index of the first string is`1`.`FIELD()`is complementary to`ELT()`.FIELD('b','a','b','c') 2 FIELD('d','a','b','c') 0 FIELD(NULL,'a','b','c') 0 FIELD(ELT(2,'a','b','c'),'a','b','c') 2

`FIND_IN_SET(``str,str_list``)``str_list`is a string consisting of substrings separated by commas (that is, it is like a`SET`value).`FIND_IN_SET()`returns the index of`str`within`str_list`. Returns`0`if`str`is not present in`str_list`or`NULL`if either argument is`NULL`. The index of the first substring is`1`.FIND_IN_SET('cow','moose,cow,pig') 2 FIND_IN_SET('dog','moose,cow,pig') 0

`FORMAT(``x,d``)`Formats the number

`x`to`d`decimals using a format like`'``nn,nnn.nnn``'`and returns the result as a string. If`d`is`0`, the result has no decimal point or fractional part.FORMAT(1234.56789,3) '1,234.568' FORMAT(999999.99,2) '999,999.99' FORMAT(999999.99,0) '1,000,000'

Note the rounding behavior exhibited by the final example.

`HEX(``n``)``HEX(``str``)`With a numeric argument

`n`,`HEX()`returns the value of the argument in hexadecimal form, as a string. The following two expressions are equivalent:HEX(n) CONV(n,10,16)

See the description of

`CONV()`for more information.Prior to MySQL 4.0.1,

`HEX()`always interprets its argument as a string.HEX(255) 'FF' HEX('255') 'FF'

As of MySQL 4.0.1,

`HEX()`can accept a string argument and returns a string consisting of each character in the argument represented as two hex digits:HEX('255') '323535' HEX('abc') '616263'

`HEX()`was introduced in MySQL 3.22.4.`INSERT(``str,pos,len,ins_str``)`Returns the string

`str`, with the substring beginning at position`pos`and`len`characters long replaced by the string`ins_str`. Returns the original string if`pos`is out of range or`NULL`if any argument is`NULL`.INSERT('nighttime',6,4,'fall') 'nightfall' INSERT('sunshine',1,3,'rain or ') 'rain or shine' INSERT('sunshine',0,3,'rain or ') 'sunshine'

`INSTR(``str,substr``)``INSTR()`is like the two-argument form of`LOCATE()`, but with the arguments reversed. The following two expressions are equivalent:INSTR(str,substr) LOCATE(substr,str)

`LCASE(``str``)`This function is a synonym for

`LOWER()`.`LEFT(``str,len``)`Returns the leftmost

`len`characters from the string`str`or the entire string if there aren't that many characters. Returns`NULL`if`str`is`NULL`. Returns the empty string if`len`is`NULL`or less than`1`.LEFT('my left foot', 2) 'my' LEFT(NULL,10) NULL LEFT('abc',NULL) '' LEFT('abc',0) ''

`LENGTH(``str``)`Returns the length of the string

`str`.LENGTH('abc') 3 LENGTH('') 0 LENGTH(NULL) NULL

`LOCATE(``substr,str``)``LOCATE(substr,str,pos)`The two-argument form of

`LOCATE()`returns the position of the first occurrence of the string`substr`within the string`str`or`0`if`substr`does not occur within`str`. Returns`NULL`if any argument is`NULL`. If the position argument`pos`is given,`LOCATE()`starts looking for`substr`at that position. As of MySQL 4.0.1, the test is not case sensitive unless either argument is a binary string:LOCATE('b','abc') 2 LOCATE('b','ABC') 2 LOCATE(BINARY 'b','ABC') 0

Prior to MySQL 4.0.1, the test is case sensitive:

LOCATE('b','abc') 2 LOCATE('b','ABC') 0

`LOWER(``str``)`Returns the string

`str`with all the characters converted to lowercase or`NULL`if`str`is`NULL`.LOWER('New York, NY') 'new york, ny' LOWER(NULL) NULL

`LPAD(``str,len,pad_str``)`Returns a string consisting of the value of the string

`str`, left-padded with the string`pad_str`to a length of`len`characters. Returns`NULL`if any argument is`NULL`.LPAD('abc',12,'def') 'defdefdefabc' LPAD('abc',10,'.') '.......abc'

As of MySQL 3.23.29,

`LPAD()`shortens the result to`len`characters:LPAD('abc',2,'.') 'ab'

Prior to MySQL 3.23.29,

`LPAD()`returns`str`if`str`is already`len`characters long:LPAD('abc',2,'.') 'abc'

`LPAD()`was introduced in MySQL 3.22.2.`LTRIM(``str``)`Returns the string

`str`with leftmost (leading) spaces removed or`NULL`if`str`is`NULL`.LTRIM(' abc ') 'abc '

`MAKE_SET(``n,bit0_str,bit1_str``,...)`Constructs a

`SET`value (a string consisting of substrings separated by commas) based on the value of the integer`n`and the strings`bit0_str,bit1_str``, ...`For each bit that is set in the value of`n`, the corresponding string is included in the result. (If bit`0`is set, the result includes`bit0_str`, and so on.) If`n`is`0`, the result is the empty string. If`n`is`NULL`, the result is`NULL`. If any string in the list is`NULL`, it is ignored when constructing the result string.MAKE_SET(8,'a','b','c','d','e') 'd' MAKE_SET(7,'a','b','c','d','e') 'a,b,c' MAKE_SET(2+16,'a','b','c','d','e') 'b,e' MAKE_SET(2|16,'a','b','c','d','e') 'b,e' MAKE_SET(-1,'a','b','c','d','e') 'a,b,c,d,e'

The last example selects every string because the value

`?1`has all bits turned on.`MAKE_SET()`was introduced in MySQL 3.22.2.`MATCH(``column_list``) AGAINST(``str)``MATCH(``column_list``) AGAINST(``str``IN BOOLEAN MODE)``MATCH`performs a search operation using a`FULLTEXT`index. The`MATCH`list consists of one or more column names separated by commas. These must be the columns that make up a`FULLTEXT`index on the table you are searching. The`str`argument to`AGAINST()`indicates the word or words to search for in the given columns. Words are sequences of characters made up of letters, digits, single quotes, or underscores. The parentheses are optional for`MATCH`, but not for`AGAINST`.`MATCH`produces a relevance ranking for each row. Ranks are non-negative floating-point numbers, with a rank of zero indicating that the search words were not found. Positive values indicate that at least one search word was found. Words that are present in the more than half the rows of the table are considered to have zero relevance because they are so common. In addition, MySQL has an internal list of stop words (such as "the" and "but") that are never considered relevant.If the search string is followed by

`IN BOOLEAN MODE`, the search results are based purely on absence or presence of the search words without regard to how often they occur in the table. For boolean searches, words in the search string can be modified with the following operators to affect how the search is done:`+`or`-`A leading

`+`or`-`indicates that the word must be present or absent.`<`or`>`A leading

`<`or`>`decreases or increases a word's contribution to the relevance value calculation.`~`A leading

`~`negates a word's contribution to the relevance value calculation, but does not exclude rows containing the word entirely, as`-`would.`*`A trailing

`*`acts as a wildcard operator. For example,`act*`matches`act`,`acts`,`action`, and so on.`"``phrase``"`A phrase search can be performed by surrounding the phrase within double quotes. Each word must be present in the order given in the phrase.

`()`Parentheses group words into expressions.

Words with no modifiers are treated as optional in a boolean search, just as for non-boolean searches.

It's possible to perform a boolean-mode search in the absence of a

`FULLTEXT`index, but this can be quite slow.`FULLTEXT`searching was introduced in MySQL 3.23.23, boolean mode searches in MySQL 4.0.1 and phrase searching in MySQL 4.0.2.More information on

`FULLTEXT`searching can be found in Chapter 3, "MySQL SQL Syntax and Use."MID(str,pos,len) MID(str,pos)

The three-argument form returns a substring of the string

`str`, beginning at position`pos`and`len`characters long. The two-argument form returns the substring beginning at`pos`to the end of the string. Returns`NULL`if any argument is`NULL`.MID('what a dull example',8,4) 'dull' MID('what a dull example',8) 'dull example'

`MID()`is actually a synonym for`SUBSTRING()`and can be used with any of the forms of syntax that`SUBSTRING()`allows.`OCT(``n``)`Returns the value of

`n`in octal form, as a string. The following two expressions are equivalent:OCT(n) CONV(n,10,8)

See the description of

`CONV()`for more information.`OCT()`was introduced in MySQL 3.22.4.`OCTET_LENGTH(``str``)`This function is a synonym for

`LENGTH()`.`POSITION(``substr``IN``str``)`This is like th