SQL Tutorial SQL Advanced SQL Database SQL References

MySQL Functions



MySQL has many built-in functions. For easy reference, a list of all MySQL functions are provided. These functions can be used in queries in MySQL and can also be used within the programming environment provided by the MySQL database, such as stored procedures, functions, triggers, etc.

Below is the list of MySQL functions, sorted by category:

MySQL String Functions

FunctionDescription
ASCII()Returns the ASCII value for the specific character
BIT_LENGTH()Return length of argument in bits
CHAR()Return the character for each integer passed
CHAR_LENGTH()Returns the length of a string (in characters)
CHARACTER_LENGTH()Returns the length of a string (in characters)
CONCAT()Adds two or more expressions together
CONCAT_WS()Adds two or more expressions together with a separator
EXPORT_SET()Returns a string which contains an ON string for every bit set in the value bits, and an OFF string for every unset bit in the value bits.
FIELD()Returns the index position of a value in a list of values
FIND_IN_SET()Returns the position of a string within a list of strings
FORMAT()Formats a number to a format like "#,###,###.##", rounded to a specified number of decimal places
INSERT()Inserts a string within a string at the specified position and for a certain number of characters
INSTR()Returns the position of the first occurrence of a string in another string
LCASE()Converts a string to lowercase
LEFT()Extracts a number of characters from a string (starting from left)
LENGTH()Returns the length of a string (in bytes)
LOCATE()Returns the position of the first occurrence of a substring in a string
LOWER()Converts a string to lowercase
LPAD()Left-pads a string with another string, to a certain length
LTRIM()Removes leading spaces from a string
MAKE_SET()Return a set of comma-separated strings that have the corresponding bit in bits set
MID()Extracts a substring from a string (starting at any position)
OCTET_LENGTH()Returns the length of a string (in bytes)
ORD()Return character code for leftmost character of the argument
POSITION()Returns the position of the first occurrence of a substring in a string
QUOTE()Escape the argument for use in an SQL statement
REPEAT()Repeats a string as many times as specified
REPLACE()Replaces all occurrences of a substring within a string, with a new substring
REVERSE()Reverses a string and returns the result
RIGHT()Extracts a number of characters from a string (starting from right)
RPAD()Right-pads a string with another string, to a certain length
RTRIM()Removes trailing spaces from a string
SPACE()Returns a string of the specified number of space characters
STRCMP()Compares two strings
SUBSTR()Extracts a substring from a string (starting at any position)
SUBSTRING()Extracts a substring from a string (starting at any position)
SUBSTRING_INDEX()Returns a substring of a string before a specified number of delimiter occurs
TRIM()Removes leading and trailing spaces from a string
UCASE()Converts a string to uppercase
UPPER()Converts a string to uppercase

MySQL REGEXP Functions and Operators

NameDescription
NOT REGEXPNegation of REGEXP
NOT RLIKENegation of RLIKE
REGEXPUsed to check whether string matches regular expression
REGEXP_INSTR()Starting index of substring matching regular expression
REGEXP_LIKE()Whether string matches regular expression
REGEXP_REPLACE()Replace substrings matching regular expression
REGEXP_SUBSTR()Return substring matching regular expression
RLIKEUsed to check whether string matches regular expression

MySQL Math/Numeric Functions

FunctionDescription
ABS()Returns the absolute value of a number
ACOS()Returns the arc cosine of a number
ASIN()Returns the arc sine of a number
ATAN()Returns the arc tangent of one or two numbers
ATAN2()Returns the arc tangent of two numbers
AVG()Returns the average value of an expression
CEIL()Returns the smallest integer value that is >= to a number
CEILING()Returns the smallest integer value that is >= to a number
COS()Returns the trigonometric cosine of an angle
COT()Returns the trigonometric cotangent of an angle
COUNT()Returns the number of records returned by a select query
CRC32()Computes a cyclic redundancy check value
DEGREES()Converts a value in radians to degrees
DIV()Used for integer division
EXP()Returns e raised to the power of a specified number
FLOOR()Returns the largest integer value that is <= to a number
GREATEST()Returns the greatest value of the list of arguments
LEAST()Returns the smallest value of the list of arguments
LN()Returns the natural logarithm of a number
LOG()Returns the natural logarithm of a number, or the logarithm of a number to a specified base
LOG10()Returns the base-10 logarithm of a number
LOG2()Returns the base-2 logarithm of a number
MAX()Returns the maximum value in a set of values
MIN()Returns the minimum value in a set of values
MOD()Returns the remainder of a number divided by another number
PI()Returns the value of PI
POW()Returns the value of a number raised to the power of another number
POWER()Returns the value of a number raised to the power of another number
RADIANS()Converts a degree value into radians
RAND()Returns a random number
ROUND()Rounds a number to a specified number of decimal places
SIGN()Returns the sign of a number
SIN()Returns the trigonometric sine of an angle
SQRT()Returns the square root of a number
SUM()Calculates the sum of a set of values
TAN()Returns the trigonometric tangent of an angle
TRUNCATE()Truncates a number to the specified number of decimal places

MySQL Date/Time Functions

FunctionDescription
ADDDATE()Adds a time/date interval to a date/datetime and then returns the date/datetime
ADDTIME()Adds a time interval to a time/datetime and then returns the time/datetime value
CONVERT_TZ()Convert a date/datetime value from one time zone to another
CURDATE()Returns the current date
CURRENT_DATE()Returns the current date
CURRENT_TIME()Returns the current time
CURRENT_TIMESTAMP()Returns the current date and time
CURTIME()Returns the current time
DATE()Extracts the date part from a date/datetime expression
DATEDIFF()Returns the number of days between two date/datetime values
DATE_ADD()Adds a time/date interval to a date and then returns the date
DATE_FORMAT()Formats a date or datetime value by a specified format
DATE_SUB()Subtracts a time/date interval from a date and then returns the date
DAY()Returns the day of the month for a given date/datetime value
DAYNAME()Returns the weekday name for a given date/datetime value
DAYOFMONTH()Returns the day of the month for a given date/datetime value
DAYOFWEEK()Returns the weekday index for a given date/datetime value
DAYOFYEAR()Returns the day of the year for a given date/datetime value
EXTRACT()Extracts a part from a given date
FROM_DAYS()Returns a date from a numeric date value
FROM_UNIXTIME()Returns a date/datetime value from a given Unix timestamp
GET_FORMAT()Returns a format string
HOUR()Returns the hour part for a given date/datetime value
LAST_DAY()Extracts the last day of the month for a given date/datatime value
LOCALTIME()Returns the current date and time
LOCALTIMESTAMP()Returns the current date and time
MAKEDATE()Creates and returns a date based on a year and a number of days value
MAKETIME()Creates and returns a time based on an hour, minute, and second value
MICROSECOND()Returns the microsecond part of a time/datetime value
MINUTE()Returns the minute part of a time/datetime value
MONTH()Returns the month part for a given date/datetime value
MONTHNAME()Returns the name of the month for a given date/datetime value
NOW()Returns the current date and time
PERIOD_ADD()Adds a specified number of months to a period
PERIOD_DIFF()Returns the difference between two periods
QUARTER()Returns the quarter of the year for a given date/datetime value
SECOND()Returns the second part of a time/datetime value
SEC_TO_TIME()Returns a time value based on the specified seconds
STR_TO_DATE()Returns a date based on a string and a format
SUBDATE()Subtracts a time/date interval from a date/datetime and then returns the date/datetime
SUBTIME()Subtracts a time interval from a time/datetime and then returns the time/datetime value
SYSDATE()Returns the current date and time
TIME()Extracts the time part from a given time/datetime value
TIME_FORMAT()Formats a time or datetime value by a specified format
TIME_TO_SEC()Converts a time value into seconds
TIMEDIFF()Returns the difference between two time/datetime expressions
TIMESTAMP()Returns a datetime value based on a date or datetime value
TIMESTAMPADD()Adds an interval to a date/datetime value
TIMESTAMPDIFF()Subtracts an interval from a date/datetime value
TO_DAYS()Returns the number of days between a date/datetime and date "0000-00-00"
TO_SECONDS()Return the date or datetime argument converted to seconds since Year 0
UNIX_TIMESTAMP()Returns Unix timestamp
UTC_DATE()Returns the current UTC date
UTC_TIME()Returns the current UTC time
UTC_TIMESTAMP()Returns the current UTC date and time
WEEK()Returns the week number for a given date/datetime value
WEEKDAY()Returns the weekday number for a given date/datetime value
WEEKOFYEAR()Returns the week number for a given date/datetime value
YEAR()Returns the year part for a given date/datetime value
YEARWEEK()Returns the year and week number for a given date/datetime value

MySQL Advanced Functions

FunctionDescription
BIN()Returns a binary representation of a number
BINARY()Converts a value to a binary string
CASE()Goes through conditions and return a value when the first condition is met
CAST()Converts a value (of any type) into a specified datatype
COALESCE()Returns the first non-null value in a list
CONNECTION_ID()Returns the unique connection ID for the current connection
CONV()Converts a number from one numeric base system to another
CONVERT()Converts a value into the specified datatype or character set
CURRENT_USER()Returns the user name and host name for the MySQL account that the server used to authenticate the current client
DATABASE()Returns the name of the current database
ELT()Returns string at specified index number
FROM_BASE64()Decode base-64 encoded string and return result
HEX()Returns a string containing hexadecimal representation of decimal or string value
IF()Returns a value if a condition is TRUE, or another value if a condition is FALSE
IFNULL()Return a specified value if the expression is NULL, otherwise return the expression.
ISNULL()Returns 1 or 0 depending on whether an expression is NULL
LAST_INSERT_ID()Returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table
NULLIF()Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned
OCT()Returns a string containing octal representation of a number
SESSION_USER()Returns the current MySQL user name and host name
SOUNDEX()Returns a soundex string
SOUNDS LIKECompare sounds
SYSTEM_USER()Returns the current MySQL user name and host name
TO_BASE64()Return the argument converted to a base-64 string
UNHEX()Return a string containing hex representation of a number
USER()Returns the current MySQL user name and host name
VERSION()Returns the current version of the MySQL database

MySQL Encryption Functions

FunctionDescription
ENCRYPT()Encrypts a string using UNIX crypt()
MD5()Returns an MD5 128-bit checksum representation of a string
OLD_PASSWORD()Used by the authentication system in MySQL to generate a hashed password from a plaintext password string
PASSWORD()Used by the authentication system in MySQL to generate a hashed password from a plaintext password string using more powerful hashing techniques
SHA()Returns an SHA-1 160-bit checksum representation of a string
SHA1()Returns an SHA-1 160-bit checksum representation of a string
SHA2()Calculates the SHA-2 family of hash functions