Functions
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.
Comparison Functions
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
Cast Functions
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
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 232?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 ex, 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 xy; 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
String Functions
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 nth string is NULL, or there is no nth 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
