General functions operate on one or more discrete values. We have omitted a few rarely used functions with very specialized applications.
ABS(
number
)
Returns the absolute value of number
(e.g., ABS(-10)
returns 10).
ACOS(
number
)
Returns the inverse cosine of number
in
radians (e.g., ACOS(0)
returns 1.570796).
ADDDATE(
date,
INTERVAL
amount
type
)
Synonym for DATE_ADD
.
ASCII(
char
)
Returns the ASCII value of the given character (e.g.,
ASCII('h')
returns 104).
ASIN(
number
)
Returns the inverse sine of number
in
radians (e.g., ASIN(0)
returns 0.000000).
ATAN(
number
)
Returns the inverse tangent of number in radians (e.g.,
ATAN(1)
returns 0.785398).
ATAN2(
X, Y
)
Returns the inverse tangent of the point
(
X
,
Y
)
(for example, ATAN(-3,3)
returns -0.785398).
BENCHMARK(
num
,
function
)
Runs function
over and over
num
times and reports the total elapsed
clock time.
BIN(
decimal
)
Returns the binary value of the given decimal number (e.g.,
BIN(8)
returns 1000). This is equivalent to the
function CONV(decimal,10,2)
.
BIT_COUNT(
number
)
Returns the number of bits that are set to 1 in the binary
representation of the number (e.g., BIT_COUNT(17)
returns 2).
BIT_LENGTH(
string
)
Returns the number of bits in string
(the
number of characters times 8, for single-byte characters).
CASE
value
WHEN
choice
THEN
returnvalue
... ELSE
returnvalue
END
Compares value
to a series of
choice
values or expressions. The first
choice
to match the
value
ends the function and returns the
corresponding returnvalue
. The
ELSE
returnvalue
is
returned if no choice
matches.
CEILING(
number
)
Returns the smallest integer greater than or equal to
number
(e.g., CEILING
(5.67)
returns 6).
CHAR(
num1
[,
num2,
. . .])
Returns a string made from converting each number to the character
corresponding to that ASCII value (e.g., CHAR(122)
returns 'z'
).
CHAR_LENGTH(
string
)
Synonym for LENGTH()
.
CHARACTER_LENGTH(
string
)
Synonym for LENGTH()
.
COALESCE(
expr1, expr2, ...
)
Returns the first non-null expression in the list (e.g.,
COALESCE(NULL,
NULL, 'cheese',
2)
returns 3).
CONCAT(
string1,string2
[
,string3,
. . .])
Returns the string formed by joining together all of the arguments
(e.g., CONCAT('Hi','
','Mom','!')
returns “Hi
Mom!”).
CONCAT_WS(
sep
,
string1
, [
string2, ...
])
Returns all strings as a single string, separated by
sep
.
CONNECTION_ID()
Returns the ID of the current connection.
CONV(
number, base1, base2
)
Returns the value of number
converted from
base1
to base2
.
number
must be an integer value (either as
a bare number or as a string). The bases can be any integer from 2 to
36. Thus, CONV(8,10,2)
returns 1000, which is the
number 8 in decimal converted to binary.
COS(
radians
)
Returns the cosine of the given number, which is in radians (e.g.,
COS(0)
returns 1.000000).
COT(
radians
)
Returns the cotangent of the given number, which must be in radians
(e.g., COT(1)
returns 0.642093).
CURDATE( )
Returns the current date. A number of the form
YYYYMMDD
is returned if this is used in a
numerical context; otherwise, a string of the form
'YYYY-MM-DD'
is returned (e.g., CURDATE(
)
could return
“1998-08-24”).
CURRENT_DATE( )
Synonym for CURDATE()
.
CURRENT_TIME( )
Synonym for CURTIME()
.
CURRENT_TIMESTAMP( )
Synonym for NOW()
.
CURTIME( )
Returns the current time. A number of the form
HHMMSS
is returned if this is used in a numerical
context; otherwise, a string of the form HH:MM:SS
is returned (e.g., CURTIME( )
could return
13:02:43).
DATABASE( )
Returns the name of the current database (e.g., DATABASE(
)
could return “mydata”).
DATE_ADD(
date,
INTERVAL
amount
type
)
Returns a date formed by adding the given amount of time to the given
date. The time element to add can be one of the following:
SECOND
, MINUTE
,
HOUR
, DAY
,
MONTH
, YEAR
,
MINUTE_SECOND
(as
“minutes:seconds”),
HOUR_MINUTE
(as
“hours:minutes”),
DAY_HOUR
(as “days
hours”), YEAR_MONTH
(as
“years-months”),
HOUR_SECOND
(as
“hours:minutes:seconds”),
DAY_MINUTE
(as “days
hours:minutes”) and DAY_SECOND
(as “days hours:minutes:seconds”).
Except for those time elements with forms specified above, the amount
must be an integer value (e.g., DATE_ADD("1998-08-24
13:00:00", INTERVAL 2 MONTH)
returns
“1998-10-24 13:00:00”).
DATE_FORMAT(
date, format
)
Returns the date formatted as specified. The format string prints as given with the following values substituted:
%a
Short weekday name (Sun, Mon, etc.)
%b
Short month name (Jan, Feb, etc.)
%D
Day of the month with ordinal suffix (1st, 2nd, 3rd, etc.)
%d
Day of the month
%H
24-hour hour (always two digits, e.g., 01)
%h
/%I
12-hour hour (always two digits, e.g., 09)
%i
Minutes
%j
Day of the year
%k
24-hour hour (one or two digits, e.g., 1)
%l
12-hour hour (one or two digits, e.g., 9)
%M
Name of the month
%m
Number of the month (January is 1)
%p
A.M. or P.M.
%r
12-hour total time (including A.M./P.M.)
%S
Seconds (always two digits, e.g., 04)
%s
Seconds (one or two digits, e.g., 4)
%T
24-hour total time
%U
Week of the year (new weeks begin on Sunday)
%W
Name of the weekday
%w
Number of weekday (0 is Sunday)
%Y
Four-digit year
%y
Two-digit year
%%
A literal %
character
DATE_SUB(
date,
INTERVAL
amount
type
)
Returns a date formed by subtracting the given amount of time from
the given date. The same interval types are used as with
DATE_ADD
(e.g., SUBDATE("1999-05-20
11:04:23", INTERVAL 2 DAY)
returns
“1999-05-18 11:04:23”).
DAYNAME(
date
)
Returns the name of the day of the week for the given date (e.g.,
DAYNAME('1998-08-22')
returns
“Saturday”).
DAYOFMONTH(
date
)
Returns the day of the month for the given date (e.g.,
DAYOFMONTH('1998-08-22')
returns 22).
DAYOFWEEK(
date
)
Returns the number of the day of the week (1 is Sunday) for the given
date (e.g., DAY_OF_WEEK('1998-08-22')
returns 7).
DAYOFYEAR(
date
)
Returns the day of the year for the given date (e.g.,
DAYOFYEAR('1983-02-15')
returns 46).
DECODE(
blob, passphrase
)
Decodes encrypted binary data using the specified passphrase. The
encrypted binary is expected to be encrypted with the
ENCODE( )
function:
mysql> SELECT DECODE(ENCODE('open sesame', 'please'), 'please'),
+---------------------------------------------------+
| DECODE(ENCODE('open sesame', 'please'), 'please') |
+---------------------------------------------------+
| open sesame |
+---------------------------------------------------+
1 row in set (0.01 sec)
DEGREES(
radians
)
Returns the given argument converted from radians to degrees (e.g.,
DEGREES(2*PI( ))
returns 360.000000).
ELT(
number,string1,string2,
. . .
)
Returns string1
if
number
is 1,
string2
if
number
is 2, etc. A null value is returned
if number
does not correspond with a
string (e.g., ELT(3,
"once","twice","thrice","fourth")
returns
“thrice”).
ENCODE(
secret, passphrase
)
Creates a binary encoding of the secret
using the passphrase
. You may later decode
the secret using DECODE( )
and the passphrase.
ENCRYPT(
string
[
,
salt
])
Password-encrypts the given string. If a salt is provided, it is used
to add extra obfusticating characters to the encrypted string (e.g.,
ENCRYPT('mypass','3a')
could return
“3afi4004idgv”).
EXP(
power
)
Returns the number e raised to the given power
(e.g., EXP(1)
returns 2.718282).
EXPORT_SET(
num, on, off,
[
separator,
[
num_bits
]])
Examines a number and maps the on and off bits in that number to the
strings specified by the on
and
off
arguments. In other words, the first string in
the output indicates the on
/off
value of the first (low-order) bit of num
,
the second string reflects the second bit, and so on. Examples:
mysql> SELECT EXPORT_SET(5, "y", "n", "", 8); +--------------------------------+ | EXPORT_SET(5, "y", "n", "", 8) | +--------------------------------+ | ynynnnnn | +--------------------------------+ 1 row in set (0.00 sec) mysql> SELECT EXPORT_SET(5, "y", "n", ",", 8); +---------------------------------+ | EXPORT_SET(5, "y", "n", ",", 8) | +---------------------------------+ | y,n,y,n,n,n,n,n | +---------------------------------+ 1 row in set (0.00 sec)
EXTRACT(interval FROM datetime)
Returns the specified part of a DATETIME
(e.g.,
EXTRACT(YEAR
FROM
'2001-08-10
19:45:32'
) returns
2001).
FIELD(
string,string1,string2,
. . .
)
Returns the position in the argument list (starting with
string1
) of the first string that is
identical to string
. Returns 0 if no other
string matches string
(e.g.,
FIELD('abe','george','john','abe','bill')
returns
3).
FIND_IN_SET(
string,set
)
Returns the position of string
within
set
. The set
argument is a series of strings separated by commas (e.g.,
FIND_IN_SET ('abe',
'george, john, abe,
bill')
returns 3).
FLOOR(
number
)
Returns the largest integer less than or equal to
number
(e.g.,
FLOOR(5.67)
returns 5).
FORMAT(
number,
decimals
)
Neatly formats the given number, using the given number of decimals
(e.g., FORMAT(4432.99134,2)
returns
“4,432.99”).
FROM_DAYS(
days
)
Returns the date that is the given number of days (in which day 1 is
Jan 1 of year 1) (e.g.,
FROM_DAYS(728749)
returns
“1995-04-02”).
FROM_UNIXTIME(
seconds
[
,
format
])
Returns the date (in GMT
) corresponding to the
given number of seconds since the epoch (January 1, 1970
GMT
). For example,
FROM_UNIXTIME(903981584)
returns
“1998-08-24 18:00:02”. If a format
string (using the same format as DATE_FORMAT
) is
given, the returned time is formatted accordingly.
GET_LOCK(
name,seconds
)
Creates a named user-defined lock that waits for the given number of
seconds until timeout. This lock can be used for client-side
application locking between programs that cooperatively use the same
lock names. If the lock is successful, 1 is returned. If the lock
times out while waiting, 0 is returned. All others errors return
NULL
values. Only one named lock may be active at
a time during a single session. Running GET_LOCK(
)
more than once will silently remove any previous locks.
For example: GET_LOCK("mylock",10)
could return 1
within the following 10 seconds.
GREATEST(
num1, num2
[
, num3,
. . . ])
Returns the numerically highest of all the arguments (for example,
GREATEST(5,6,68,1,-300)
returns 68).
HEX(
decimal
)
Returns the hexadecimal value of the given decimal number (e.g.,
HEX(90)
returns
“3a”). This is equivalent to the
function CONV(decimal,10,16)
.
HOUR(
time
)
Returns the hour of the given time (e.g.,
HOUR('15:33:30')
returns 15).
IF(
test, value1, value2
)
If test
is true, returns
value1
, otherwise returns
value2
(e.g.,
IF(1>0,"true","false")
returns
true
).
IFNULL(
value, value2
)
Returns value
if it is not null;
otherwise, returns value2
(e.g.,
IFNULL(NULL,
"bar")
returns
“bar”).
INSERT(
string,position,length,new
)
Returns the string created by replacing the substring of
string
starting at
position
and going
length
characters with the string
new
(e.g.,
INSERT('help',3,1,'
can
jum')
returns “he can
jump”).
INSTR(
string,substring
)
Identical to LOCATE
except that the arguments are
reversed (e.g., INSTR('makebelieve','lie')
returns
7).
INTERVAL(
A,B,C,D,
. . . )
Returns 0 if A
is the smallest value, 1 if
A
is between B
and C, 2 if A
is between
C
and D
, etc.
All values except for A
must be in order
(e.g., INTERVAL(5,2,4,6,8)
returns 2, because 5 is
in the second interval, between 4 and 6).
ISNULL(
expression
)
Returns 1 if the expression evaluates to NULL
;
otherwise, returns 0 (e.g., ISNULL(3)
returns 0).
LAST_INSERT_ID( )
Returns the last value that was automatically generated for an
AUTO_INCREMENT
field (e.g.,
LAST_INSERT_ID( )
could return 4).
LCASE(
string
)
Synonym for LOWER()
.
LEAST(
num1, num2
[
, num3,
. . .])
Returns the numerically smallest of all the arguments (for example,
LEAST(5,6,68,1,-20)
returns -20).
LEFT(
string,length
)
Returns length
characters from the left
end of string
(e.g.,
LEFT("12345",3)
returns
“123”).
LENGTH(
string
)
Returns the length of string
(e.g.,
CHAR_LENGTH('Hi Mom!')
returns 7). In character
sets that use multi-byte characters (such as Unicode and several
Asian character sets), one character may take up more than one byte.
In these cases, MySQL’s string functions should
correctly count the number of characters, not bytes, in the string.
However, in versions prior to 3.23, this did not work properly and
the function returned the number of bytes.
LOAD_FILE(
filename
)
Reads the contents of the specified file as a string. This file must
exist on the server and be world readable. Naturally, you must also
have FILE
privileges.
LOCATE(
substring,string
[
,number
])
Returns the character position of the first occurrence of
substring
within
string
(e.g.,
LOCATE('SQL','MySQL')
returns 3). If
substring
does not exist in
string
, 0 is returned. If a numerical
third argument is supplied to LOCATE
, the search
for substring
within
string
does not start until the given
position within string
.
LOG(
number
)
Returns the natural logarithm of number
(e.g., LOG(2)
returns 0.693147).
LOG10(
number
)
Returns the common logarithm of number
(e.g., LOG10(1000)
returns 3.000000).
LOWER(
string
)
Returns string
with all characters turned
into lowercase (e.g., LOWER('BoB')
returns
“bob”).
LPAD(
string,length,padding
)
Returns string
with
padding
added to the left end until the new string
is length
characters long (e.g.,
LPAD('
Merry X-Mas',18,'Ho')
returns “HoHoHo Merry X-Mas”).
LTRIM(
string
)
Returns string
with all leading whitespace
removed (e.g., LTRIM('
Oops')
returns “Oops”).
MAKE_SET(
bits, string1, string2, ...
)
Creates a MySQL SET
based on the binary
representation of a number by mapping the on bits in the number to
string values. The first string will appear in the output if the
first (low-order) bit of bits
is set, the
second string will appear if the second bit is set, and so on.
Example:
mysql> SELECT MAKE_SET(5, "a", "b", "c", "d", "e", "f");
+-------------------------------------------+
| MAKE_SET(5, "a", "b", "c", "d", "e", "f") |
+-------------------------------------------+
| a,c |
+-------------------------------------------+
1 row in set (0.01 sec)
MD5(
string
)
Creates an MD5 checksum for the specified string. The MD5 checksum is always a string of 32 hexadecimal numbers.
MID(
string,position,length
)
Synonym for SUBSTRING()
with three arguments.
MINUTE(
time
)
Returns the minute of the given time (e.g.,
MINUTE('15:33:30')
returns 33).
MOD(
num1, num2
)
Returns the modulo of num1
divided by
num2
. This is the same as the
%
operator (e.g., MOD(11,3)
returns 2).
MONTH(
date
)
Returns the number of the month (1 is January) for the given date
(e.g., MONTH('1998-08-22')
returns 8).
MONTHNAME(
date
)
Returns the name of the month for the given date (e.g.,
MONTHNAME('1998-08-22')
returns
“August”).
NOW()
Returns the current date and time. A number of the form
YYYYMMDDHHMMSS
is returned if this is used in a
numerical context; otherwise, a string of the form
'YYYY-MM-DD HH:MM:SS'
is returned (e.g.,
NOW( )
could return “1998-08-24
12:55:32”).
NULLIF(
value, value2
)
Return NULL
if value
and value2
are equal, or else returns
value
(e.g.,
NULLIF((5+3)18,1)
returns
NULL
).
OCT(
decimal
)
Returns the octal value of the given decimal number (e.g.,
OCT(8)
returns 10). This is equivalent to the
function CONV(decimal,10,8)
.
OCTET_LENGTH(
string
)
Synonym for LENGTH()
.
ORD(
string
)
Returns a numeric value corresponding to the first character in
string
. Treats a multi-byte string as a
number in base 256. Thus, an 'x'
in the first byte
is worth 256 times as much as an 'x'
in the second
byte.
PASSWORD(
string
)
Returns a password-encrypted version of the given string (e.g.,
PASSWORD('mypass')
could return
“3afi4004idgv”).
PERIOD_ADD(
date,months
)
Returns the date formed by adding the given number of months to
date
(which must be of the form
YYMM
or YYYYMM
) (e.g.,
PERIOD_ADD(9808,14)
returns 199910).
PERIOD_DIFF(
date1, date2
)
Returns the number of months between the two dates (which must be of
the form YYMM
or YYYYMM
) (e.g.,
PERIOD_DIFF(199901,8901)
returns 120).
PI( )
Returns the value of pi: 3.141593.
POSITION(
substring,string
)
Synonym for LOCATE()
with two arguments.
POW(
num1, num2
)
Returns the value of num1
raised to the
num2
power (e.g.,
POWER(3,2)
returns 9.000000).
POWER(
num1, num2
)
Synonym for POW()
.
QUARTER(
date
)
Returns the number of the quarter of the given date (1 is
January-March) (e.g., QUARTER('1998-08-22')
returns 3).
RADIANS(
degrees
)
Returns the given argument converted from degrees to radians (e.g.,
RADIANS(-90)
returns -1.570796).
RAND([
seed
])
Returns a random decimal value between 0 and 1. If an argument is
specified, it is used as the seed of the random number generator
(e.g., RAND(3)
could return 0.435434).
RELEASE_LOCK(
name
)
Removes the named lock created with the GET_LOCK
function. Returns 1 if the release is successful, 0 if it failed
because the current thread did not own the lock, and a
null
value if the lock did not exist. For example,
RELEASE_LOCK("mylock")
.
REPEAT(
string,number
)
Returns a string consisting of the original
string
repeated
number
times. Returns an empty string if
number
is less than or equal to zero
(e.g., REPEAT('ma',4)
returns
‘mamamama').
REPLACE(
string,old,new
)
Returns a string that has all occurrences of the substring
old
replaced with
new
(e.g.,
REPLACE('
black
jack
','
ack
','
oke
')
returns “bloke joke”).
REVERSE(
string
)
Returns the character reverse of string
(e.g., REVERSE('my
bologna')
returns “angolob ym”).
RIGHT(
string,length
)
Synonym for SUBSTRING()
with
FROM
argument (e.g.,
RIGHT("string",1)
returns
“g”).
ROUND(
number
[
,decimal
])
Returns number
rounded to the given number
of decimals. If no decimal
argument is
supplied, number
is rounded to an integer
(e.g., ROUND(5.67,1)
returns 5.7).
RPAD(
string,length,padding
)
Returns string
with
padding
added to the right end until the
new string is length
characters long
(e.g., RPAD('Yo',5,'!')
returns
“Yo!!!”).
RTRIM(
string
)
Returns string
with all trailing
whitespace removed (e.g., RTRIM('Oops
')
returns
“Oops”).
SECOND(
time
)
Returns the seconds of the given time (e.g.,
SECOND('15:33:30')
returns 30).
SEC_TO_TIME(
seconds
)
Returns the number of hours, minutes, and seconds in the given number
of seconds. A number of the form HHMMSS
is
returned if this is used in a numerical context; otherwise, a string
of the form HH:MM:SS
is returned (e.g.,
SEC_TO_TIME(3666)
returns
“01:01:06”).
SESSION_USER( )
Synonym for USER()
.
SIGN(
number
)
Returns -1 if number
is negative, 0 if
it’s zero, or 1 if it’s positive
(e.g., SIGN(4)
returns 1).
SIN(
radians
)
Returns the sine of the given number, which is in radians (e.g.,
SIN(2*PI( ))
returns 0.000000).
SOUNDEX(
string
)
Returns the Soundex code associated with string (e.g.,
SOUNDEX('Jello')
returns
“J400”).
SPACE(
number
)
Returns a string that contains number
spaces (e.g., SPACE(5)
returns "
“).
SQRT(
number
)
Returns the square root of number
(e.g.,
SQRT(16)
returns 4.000000).
STRCMP(
string1, string2
)
Returns 0 if the strings are the same, -1 if
string1
would sort before
string2
, or 1 if
string1
would sort after
string2
(e.g.,
STRCMP('bob','bobbie')
returns -1).
SUBDATE(
date,
INTERVAL
amount
type
)
Synonym for DATE_SUB()
.
SUBSTRING(
string,position
)
Returns all of string
starting at
position
characters (e.g.,
SUBSTRING("123456",3)
returns
“3456”).
SUBSTRING(
string,position,length
)/SUBSTRING(
string
FROM
position
FOR
length
)
Returns the substring formed by taking
length
characters from
string
, starting at
position
(e.g.,
SUBSTRING('12345',2,3)
returns
“234”).
SUBSTRING(string FROM length)
Returns length
characters from the right
end of string
(e.g.,
SUBSTRING("12345" FROM 3)
returns
“345”).
SUBSTRING_INDEX(
string,character,number
)
Returns the substring formed by counting
number
of
character
within
string
and then returns everything to the
left if the count is positive, or everything to the right if the
count is negative (e.g.,
SUBSTRING_INDEX('1,2,3,4,5',',',3)
returns
“1,2,3”).
SYSDATE()
Synonym for NOW()
.
SYSTEM_USER()
Synonym for USER()
.
TAN(
radians
)
Returns the tangent of the given number, which must be in radians
(e.g., TAN(0)
returns 0.000000).
TIME_FORMAT(
time, format
)
Returns the given time using a format string. The format string is of
the same type as DATE_FORMAT
, as shown earlier.
TIME_TO_SEC(
time
)
Returns the number of seconds in the time
argument (e.g., TIME_TO_SEC('01:01:06')
returns
3666).
TO_DAYS(
date
)
Returns the number of days (in which day 1 is Jan 1 of year 1) to the
given date. The date may be a value of type DATE
,
DATETIME
, or TIMESTAMP
, or a
number of the form YYMMDD
or
YYYYMMDD
(e.g.,
TO_DAYS(19950402)
returns 728749).
TRIM([BOTH|LEADING|TRAILING] [
remove
] [FROM]
string
)
With no modifiers, returns string
with all
trailing and leading whitespace removed. You can specify to remove
the leading or trailing whitespace, or both. You can also specify a
character other than space to be removed (e.g.,
TRIM(both
'-'
from
'---look here---')
returns
“look here”).
TRUNCATE
(
number, decimals
)
Returns number
truncated to the given
number of decimals (for example,
TRUNCATE(3.33333333,2)
returns 3.33).
UCASE(
string
)
Synonym for UPPER()
.
UNIX_TIMESTAMP([
date
])
Returns the number of seconds from the epoch (January 1, 1970
GMT
) to the given date (in
GMT
). If no date is given, the number of seconds
to the current date is used (e.g.,
UNIX_TIMESTAMP('1998-08-24 18:00:02')
returns
903981584).
UPPER(
string
)
Returns string
with all characters turned
into uppercase (e.g., UPPER
('Scooby')
returns
“SCOOBY”).
USER()
Returns the name of the current user (e.g., SYSTEM_USER(
)
could return
“ryarger@localhost”).
VERSION( )
Returns the version of the MySQL server itself (e.g.,
VERSION( )
could return
“3.22.5c-alpha”).
WEEK(
date
)
Returns the week of the year for the given date (e.g.,
WEEK('1998-12-29')
returns 52).
WEEKDAY(
date
)
Returns the numeric value of the day of the week for the specified date. Day numbers start with Monday as 0 and end with Sunday as 6.
YEAR(
date
)
Returns the year of the given
date (e.g.,
YEAR('1998-12-29')
returns 1998).