{"id":162,"date":"2018-12-06T10:16:29","date_gmt":"2018-12-06T10:16:29","guid":{"rendered":"https:\/\/barkhane.com\/sql\/2018\/12\/06\/mysql-functions\/"},"modified":"2024-03-03T18:01:36","modified_gmt":"2024-03-03T18:01:36","slug":"mysql-functions","status":"publish","type":"post","link":"https:\/\/barkhane.com\/sql\/mysql-functions\/","title":{"rendered":"MySQL Functions"},"content":{"rendered":"

MySQL has many built-in MySQL Functions.<\/p>\n

This reference contains string, numeric, date, and some advanced functions in MySQL.<\/p>\n

MySQL String Functions<\/h2>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Function<\/strong><\/th>\nDescription<\/th>\n<\/tr>\n
ASCII<\/strong><\/td>\nReturns the ASCII value for the specific character<\/td>\n<\/tr>\n
CHAR_LENGTH<\/strong><\/td>\nReturns the length of a string (in characters)<\/td>\n<\/tr>\n
CHARACTER_LENGTH<\/strong><\/td>\nReturns the length of a string (in characters)<\/td>\n<\/tr>\n
CONCAT<\/strong><\/td>\nAdds two or more expressions together<\/td>\n<\/tr>\n
CONCAT_WS<\/strong><\/td>\nAdds two or more expressions together with a separator<\/td>\n<\/tr>\n
FIELD<\/strong><\/td>\nReturns the index position of a value in a list of values<\/td>\n<\/tr>\n
FIND_IN_SET<\/strong><\/td>\nReturns the position of a string within a list of strings<\/td>\n<\/tr>\n
FORMAT<\/strong><\/td>\nFormats a number to a format like \\”#,###,###.##\\”, rounded to a specified number of decimal places<\/td>\n<\/tr>\n
INSERT<\/strong><\/td>\nInserts a string within a string at the specified position and for a certain number of characters<\/td>\n<\/tr>\n
INSTR<\/strong><\/td>\nReturns the position of the first occurrence of a string in another string<\/td>\n<\/tr>\n
LCASE<\/strong><\/td>\nConverts a string to lower-case<\/td>\n<\/tr>\n
LEFT<\/strong><\/td>\nExtracts a number of characters from a string (starting from left)<\/td>\n<\/tr>\n
LENGTH<\/strong><\/td>\nReturns the length of a string (in bytes)<\/td>\n<\/tr>\n
LOCATE<\/strong><\/td>\nReturns the position of the first occurrence of a substring in a string<\/td>\n<\/tr>\n
LOWER<\/strong><\/td>\nConverts a string to lower-case<\/td>\n<\/tr>\n
LPAD<\/strong><\/td>\nLeft-pads a string with another string, to a certain length<\/td>\n<\/tr>\n
LTRIM<\/strong><\/td>\nRemoves leading spaces from a string<\/td>\n<\/tr>\n
MID<\/strong><\/td>\nExtracts a substring from a string (starting at any position)<\/td>\n<\/tr>\n
POSITION<\/strong><\/td>\nReturns the position of the first occurrence of a substring in a string<\/td>\n<\/tr>\n
REPEAT<\/strong><\/td>\nRepeats a string as many times as specified<\/td>\n<\/tr>\n
REPLACE<\/strong><\/td>\nReplaces all occurrences of a substring within a string, with a new substring<\/td>\n<\/tr>\n
REVERSE<\/strong><\/td>\nReverses a string and returns the result<\/td>\n<\/tr>\n
RIGHT<\/strong><\/td>\nExtracts a number of characters from a string (starting from right)<\/td>\n<\/tr>\n
RPAD<\/strong><\/td>\nRight-pads a string with another string, to a certain length<\/td>\n<\/tr>\n
RTRIM<\/strong><\/td>\nRemoves trailing spaces from a string<\/td>\n<\/tr>\n
SPACE<\/strong><\/td>\nReturns a string of the specified number of space characters<\/td>\n<\/tr>\n
STRCMP<\/strong><\/td>\nCompares two strings<\/td>\n<\/tr>\n
SUBSTR<\/strong><\/td>\nExtracts a substring from a string (starting at any position)<\/td>\n<\/tr>\n
SUBSTRING<\/strong><\/td>\nExtracts a substring from a string (starting at any position)<\/td>\n<\/tr>\n
SUBSTRING_INDEX<\/strong><\/td>\nReturns a substring of a string before a specified number of delimiter occurs<\/td>\n<\/tr>\n
TRIM<\/strong><\/td>\nRemoves leading and trailing spaces from a string<\/td>\n<\/tr>\n
UCASE<\/strong><\/td>\nConverts a string to upper-case<\/td>\n<\/tr>\n
UPPER<\/strong><\/td>\nConverts a string to upper-case<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n
\n
<\/div>\n<\/div>\n
\n

MySQL Numeric Functions<\/h2>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Function<\/strong><\/th>\nDescription<\/th>\n<\/tr>\n
ABS<\/strong><\/td>\nReturns the absolute value of a number<\/td>\n<\/tr>\n
ACOS<\/strong><\/td>\nReturns the arc cosine of a number<\/td>\n<\/tr>\n
ASIN<\/strong><\/td>\nReturns the arc sine of a number<\/td>\n<\/tr>\n
ATAN<\/strong><\/td>\nReturns the arc tangent of one or two numbers<\/td>\n<\/tr>\n
ATAN2<\/strong><\/td>\nReturns the arc tangent of two numbers<\/td>\n<\/tr>\n
AVG<\/strong><\/td>\nReturns the average value of an expression<\/td>\n<\/tr>\n
CEIL<\/strong><\/td>\nReturns the smallest integer value that is >= to a number<\/td>\n<\/tr>\n
CEILING<\/strong><\/td>\nReturns the smallest integer value that is >= to a number<\/td>\n<\/tr>\n
COS<\/strong><\/td>\nReturns the cosine of a number<\/td>\n<\/tr>\n
COT<\/strong><\/td>\nReturns the cotangent of a number<\/td>\n<\/tr>\n
COUNT<\/strong><\/td>\nReturns the number of records returned by a select query<\/td>\n<\/tr>\n
DEGREES<\/strong><\/td>\nConverts a value in radians to degrees<\/td>\n<\/tr>\n
DIV<\/strong><\/td>\nUsed for integer division<\/td>\n<\/tr>\n
EXP<\/strong><\/td>\nReturns e raised to the power of a specified number<\/td>\n<\/tr>\n
FLOOR<\/strong><\/td>\nReturns the largest integer value that is <= to a number<\/td>\n<\/tr>\n
GREATEST<\/strong><\/td>\nReturns the greatest value of the list of arguments<\/td>\n<\/tr>\n
LEAST<\/strong><\/td>\nReturns the smallest value of the list of arguments<\/td>\n<\/tr>\n
LN<\/strong><\/td>\nReturns the natural logarithm of a number<\/td>\n<\/tr>\n
LOG<\/strong><\/td>\nReturns the natural logarithm of a number, or the logarithm of a number to a specified base<\/td>\n<\/tr>\n
LOG10<\/strong><\/td>\nReturns the natural logarithm of a number to base 10<\/td>\n<\/tr>\n
LOG2<\/strong><\/td>\nReturns the natural logarithm of a number to base 2<\/td>\n<\/tr>\n
MAX<\/strong><\/td>\nReturns the maximum value in a set of values<\/td>\n<\/tr>\n
MIN<\/strong><\/td>\nReturns the minimum value in a set of values<\/td>\n<\/tr>\n
MOD<\/strong><\/td>\nReturns the remainder of a number divided by another number<\/td>\n<\/tr>\n
PI<\/strong><\/td>\nReturns the value of PI<\/td>\n<\/tr>\n
POW<\/strong><\/td>\nReturns the value of a number raised to the power of another number<\/td>\n<\/tr>\n
POWER<\/strong><\/td>\nReturns the value of a number raised to the power of another number<\/td>\n<\/tr>\n
RADIANS<\/strong><\/td>\nConverts a degree value into radians<\/td>\n<\/tr>\n
RAND<\/strong><\/td>\nReturns a random number<\/td>\n<\/tr>\n
ROUND<\/strong><\/td>\nRounds a number to a specified number of decimal places<\/td>\n<\/tr>\n
SIGN<\/strong><\/td>\nReturns the sign of a number<\/td>\n<\/tr>\n
SIN<\/strong><\/td>\nReturns the sine of a number<\/td>\n<\/tr>\n
SQRT<\/strong><\/td>\nReturns the square of a number<\/td>\n<\/tr>\n
SUM<\/strong><\/td>\nCalculates the sum of a set of values<\/td>\n<\/tr>\n
TAN<\/strong><\/td>\nReturns the tangent of a number<\/td>\n<\/tr>\n
TRUNCATE<\/strong><\/td>\nTruncates a number to the specified number of decimal places<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n

MySQL Date Functions<\/h2>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Function<\/strong><\/th>\nDescription<\/th>\n<\/tr>\n
ADDDATE<\/strong><\/td>\nAdds a time\/date interval to a date and then returns the date<\/td>\n<\/tr>\n
ADDTIME<\/strong><\/td>\nAdds a time interval to a time\/datetime and then returns the time\/datetime<\/td>\n<\/tr>\n
CURDATE<\/strong><\/td>\nReturns the current date<\/td>\n<\/tr>\n
CURRENT_DATE<\/strong><\/td>\nReturns the current date<\/td>\n<\/tr>\n
CURRENT_TIME<\/strong><\/td>\nReturns the current time<\/td>\n<\/tr>\n
CURRENT_TIMESTAMP<\/strong><\/td>\nReturns the current date and time<\/td>\n<\/tr>\n
CURTIME<\/strong><\/td>\nReturns the current time<\/td>\n<\/tr>\n
DATE<\/strong><\/td>\nExtracts the date part from a datetime expression<\/td>\n<\/tr>\n
DATEDIFF<\/strong><\/td>\nReturns the number of days between two date values<\/td>\n<\/tr>\n
DATE_ADD<\/strong><\/td>\nAdds a time\/date interval to a date and then returns the date<\/td>\n<\/tr>\n
DATE_FORMAT<\/strong><\/td>\nFormats a date<\/td>\n<\/tr>\n
DATE_SUB<\/strong><\/td>\nSubtracts a time\/date interval from a date and then returns the date<\/td>\n<\/tr>\n
DAY<\/strong><\/td>\nReturns the day of the month for a given date<\/td>\n<\/tr>\n
DAYNAME<\/strong><\/td>\nReturns the weekday name for a given date<\/td>\n<\/tr>\n
DAYOFMONTH<\/strong><\/td>\nReturns the day of the month for a given date<\/td>\n<\/tr>\n
DAYOFWEEK<\/strong><\/td>\nReturns the weekday index for a given date<\/td>\n<\/tr>\n
DAYOFYEAR<\/strong><\/td>\nReturns the day of the year for a given date<\/td>\n<\/tr>\n
EXTRACT<\/strong><\/td>\nExtracts a part from a given date<\/td>\n<\/tr>\n
FROM_DAYS<\/strong><\/td>\nReturns a date from a numeric datevalue<\/td>\n<\/tr>\n
HOUR<\/strong><\/td>\nReturns the hour part for a given date<\/td>\n<\/tr>\n
LAST_DAY<\/strong><\/td>\nExtracts the last day of the month for a given date<\/td>\n<\/tr>\n
LOCALTIME<\/strong><\/td>\nReturns the current date and time<\/td>\n<\/tr>\n
LOCALTIMESTAMP<\/strong><\/td>\nReturns the current date and time<\/td>\n<\/tr>\n
MAKEDATE<\/strong><\/td>\nCreates and returns a date based on a year and a number of days value<\/td>\n<\/tr>\n
MAKETIME<\/strong><\/td>\nCreates and returns a time based on an hour, minute, and second value<\/td>\n<\/tr>\n
MICROSECOND<\/strong><\/td>\nReturns the microsecond part of a time\/datetime<\/td>\n<\/tr>\n
MINUTE<\/strong><\/td>\nReturns the minute part of a time\/datetime<\/td>\n<\/tr>\n
MONTH<\/strong><\/td>\nReturns the month part for a given date<\/td>\n<\/tr>\n
MONTHNAME<\/strong><\/td>\nReturns the name of the month for a given date<\/td>\n<\/tr>\n
NOW<\/strong><\/td>\nReturns the current date and time<\/td>\n<\/tr>\n
PERIOD_ADD<\/strong><\/td>\nAdds a specified number of months to a period<\/td>\n<\/tr>\n
PERIOD_DIFF<\/strong><\/td>\nReturns the difference between two periods<\/td>\n<\/tr>\n
QUARTER<\/strong><\/td>\nReturns the quarter of the year for a given date value<\/td>\n<\/tr>\n
SECOND<\/strong><\/td>\nReturns the seconds part of a time\/datetime<\/td>\n<\/tr>\n
SEC_TO_TIME<\/strong><\/td>\nReturns a time value based on the specified seconds<\/td>\n<\/tr>\n
STR_TO_DATE<\/strong><\/td>\nReturns a date based on a string and a format<\/td>\n<\/tr>\n
SUBDATE<\/strong><\/td>\nSubtracts a time\/date interval from a date and then returns the date<\/td>\n<\/tr>\n
SUBTIME<\/strong><\/td>\nSubtracts a time interval from a datetime and then returns the time\/datetime<\/td>\n<\/tr>\n
SYSDATE<\/strong><\/td>\nReturns the current date and time<\/td>\n<\/tr>\n
TIME<\/strong><\/td>\nExtracts the time part from a given time\/datetime<\/td>\n<\/tr>\n
TIME_FORMAT<\/strong><\/td>\nFormats a time by a specified format<\/td>\n<\/tr>\n
TIME_TO_SEC<\/strong><\/td>\nConverts a time value into seconds<\/td>\n<\/tr>\n
TIMEDIFF<\/strong><\/td>\nReturns the difference between two time\/datetime expressions<\/td>\n<\/tr>\n
TIMESTAMP<\/strong><\/td>\nReturns a datetime value based on a date or datetime value<\/td>\n<\/tr>\n
TO_DAYS<\/strong><\/td>\nReturns the number of days between a date and date \\”0000-00-00\\”<\/td>\n<\/tr>\n
WEEK<\/strong><\/td>\nReturns the week number for a given date<\/td>\n<\/tr>\n
WEEKDAY<\/strong><\/td>\nReturns the weekday number for a given date<\/td>\n<\/tr>\n
WEEKOFYEAR<\/strong><\/td>\nReturns the week number for a given date<\/td>\n<\/tr>\n
YEAR<\/strong><\/td>\nReturns the year part for a given date<\/td>\n<\/tr>\n
YEARWEEK<\/strong><\/td>\nReturns the year and week number for a given date<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n

MySQL Advanced Functions<\/h2>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Function<\/strong><\/th>\nDescription<\/th>\n<\/tr>\n
BIN<\/strong><\/td>\nReturns a binary representation of a number<\/td>\n<\/tr>\n
BINARY<\/strong><\/td>\nConverts a value to a binary string<\/td>\n<\/tr>\n
CASE<\/strong><\/td>\nGoes through conditions and return a value when the first condition is met<\/td>\n<\/tr>\n
CAST<\/strong><\/td>\nConverts a value (of any type) into a specified datatype<\/td>\n<\/tr>\n
COALESCE<\/strong><\/td>\nReturns the first non-null value in a list<\/td>\n<\/tr>\n
CONNECTION_ID<\/strong><\/td>\nReturns the unique connection ID for the current connection<\/td>\n<\/tr>\n
CONV<\/strong><\/td>\nConverts a number from one numeric base system to another<\/td>\n<\/tr>\n
CONVERT<\/strong><\/td>\nConverts a value into the specified datatype or character set<\/td>\n<\/tr>\n
CURRENT_USER<\/strong><\/td>\nReturns the user name and host name for the MySQL account that the server used to authenticate the current client<\/td>\n<\/tr>\n
DATABASE<\/strong><\/td>\nReturns the name of the current database<\/td>\n<\/tr>\n
IF<\/strong><\/td>\nReturns a value if a condition is TRUE, or another value if a condition is FALSE<\/td>\n<\/tr>\n
IFNULL<\/strong><\/td>\nReturn a specified value if the expression is NULL, otherwise return the expression<\/td>\n<\/tr>\n
ISNULL<\/strong><\/td>\nReturns 1 or 0 depending on whether an expression is NULL<\/td>\n<\/tr>\n
LAST_INSERT_ID<\/strong><\/td>\nReturns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table<\/td>\n<\/tr>\n
NULLIF<\/strong><\/td>\nCompares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned<\/td>\n<\/tr>\n
SESSION_USER<\/strong><\/td>\nReturns the current MySQL user name and host name<\/td>\n<\/tr>\n
SYSTEM_USER<\/strong><\/td>\nReturns the current MySQL user name and host name<\/td>\n<\/tr>\n
USER<\/strong><\/td>\nReturns the current MySQL user name and host name<\/td>\n<\/tr>\n
VERSION<\/strong><\/td>\nReturns the current version of the MySQL database<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

MySQL has many built-in MySQL Functions. This reference contains string, numeric, date, and some advanced functions in MySQL. MySQL String […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[2],"tags":[],"class_list":["post-162","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts\/162","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/comments?post=162"}],"version-history":[{"count":0,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts\/162\/revisions"}],"wp:attachment":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/media?parent=162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/categories?post=162"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/tags?post=162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}