Summary -

In this topic, we described about the MySQL Functions Reference with detailed example.

MySQL has many built-in Functions like String, numeric, date, and advanced Functions that are discussed in this topic. MySQL String Functions are listed as follows –

Function Description Syntax
ASCII It returns ASCII value for the specified character. ASCII( character)
CHAR_LENGTH It returns the length of the string. CHAR_LENGTH(String)
CHARACTER_LENGTH It returns the length of the string. CHARACTER_LENGTH(String)
CONCAT It add two or more strings together to form one string CONCAT(expression1, expression2, expression3,..)
CONCAT_WS It add two or more strings together to form one string with a separator. CONCAT(separator , expression1, expression2, expression3,..)
FIELD It returns the position of value in the specified values. FIELD(value, value1, value2, value3,…)
FIND_IN_SET It returns the position of the string in String list. FIND_IN_LIST(string, string_list)
FORMAT It rounds the number with specified number of decimals. FORMAT(number, Number_of _decimals)
INSERT It Inserts a substring in the string. INSERT(String, position, Number_of_characters, substring)
INSTR It searches substring in string. INSTR(string, substring)
LCASE It converts string to lower case LCASE(string)
LEFT It extracts characters from left side of the string. LEFT(string, Number_of_characters)
LENGTH It returns the length of the string. LENGTH(String)
LOCATE It returns the position of first occurrence of sub string in the string. LOCATE(substring, string, start)
LOWER It converts string in to lower case letters. LOWER(String)
LPAD It left-pads string with another string. LPAD(string, length, lpad_string )
LTRIM It delete the starting spaces in the string. LTRIM(' string')
MID It extracts string for specified characters. MID(string, start, length)
POSITION It returns the position of first occurrence of sub string in the string. LOCATE(substring, string, start)
REPEAT It repeats the string with specified number of times. REPEAT(string, number)
REPLACE It replaces the substring in the string with another substring. REPLACE(string, substring, new_substring)
REVERSE It reverse the specified string REVERSE(string)
RIGHT It extracts characters from specified string from right side. RIGHT(string)
RPAD It right-pads the string with another string RPAD(string, length, rpad_string)
RTRIM It delete the ending spaces in the string. RTRIM('string ')
SPACE It returns the string with specified number of spaces. SPACE(number)
STRCMP It compares the two specified strings. STRCMP(string1, string2)
SUBSTR It extracts characters from the string. SUBSTR(string, start, length)
SUBSTRING It extracts characters from the string. SUBSTRING(string, start, length)
SUBSTRING_INDEX It returns sub string from string before the delimiter. SUBSTRING_INDEX(string, delimiter, number)
TRIM It removes starting and ending spaces in the string. TRIM(string)
UCASE It returns the string with upper case letters. UCASE(string)
UPPER It converts string with upper case letters. UPPER(string)

MySQL Numeric Functions are listed as follows –

Function Description Syntax
ABS It returns the absolute value for the specified value. ABS(number)
ACOS It returns the arc cosine value for the specified value. ACOS(number)
ASIN It returns the arc sine value for the specified value. ASIN(number)
ATAN It returns the arc tangent value for the specified value. ATAN(number)
ATAN2 It returns the arc tangent value for two specified values. ATAN(number1, number2)
AVG It returns the average value for specified expression. AVG(expression)
CEIL It returns the smallest integer value that is greater or equal to the value. CEIL(number)
CEILING It returns the smallest integer value that is greater or equal to the value. CEILING(number)
COS It returns the cosine value for the specified number. COS(number)
COT It returns the cotangent value for the specified value. COT(number)
COUNT It returns the number of rows from the table corresponding that SELECT statement. COUNT(expression)
DEGREES It converts the specified radians value to degrees. DEGREES(number)
DIV It is used for integer division. It returns integer value. a DIV b
EXP It returns the value with raised power of another value. EXP(number)
FLOOR It returns the largest integer value which is smaller or equal to the specified value. FLOOR(number)
GREATEST It returns the greatest value in the list of values. GREATEST(value1, value2, value3,..)
LEAST It returns the smallest value in the list of values. LEAST(value1, value2, value3,..)
LN It returns the natural logarithm of specified value. LN(number)
LOG It returns the natural logarithm of specified value or logarithm of specified base value. LOG(base, number)
LOG10 It returns the natural logarithm of specified value of base 10. LOG10(number)
LOG2 It returns the natural logarithm of specified value of base 2. LOG2(number)
MAX It returns the maximum value on specified expression of set of values. MAX(expression)
MIN It returns the minimum value on specified expression of set of values. MIN(expression)
MOD It returns the remainder of value which is divided by another value. MOD(a, b)
PI It returns the constant value of PI. PI()
POWER It returns the value raised to the power of another value POWER(a, b)
RADIANS It returns the value of degrees in to radians. RADIANS(number)
RAND It returns random number between 0 and 1. RAND(seed)
ROUND It round the specified number with decimal values. ROUND(number, decimals)
SIGN "It returns the sign of a specified number. It returns 1 when number > 0, returns 0 when number = 0, It returns -1 when number < 0" SIGN(number)
SIN It sine value for the specified number. SIN(number)
SQRT It returns the square root value of the specified number. SQRT(number)
SUM It calculates the sum of set of values on specified expression. SUM(expression)
TAN It returns tangent value for the specified value. TAN(number)
TRUNCATE It returns truncated value to the number of decimals. TRUNCATE(number, decimals)

MySQL DATE Functions are listed as follows –

Function Description Syntax
ADDDATE It adds DATE/TIME and returns the modified DATE. ADDDATE(date, INTERVAL value addunit)
ADDTIME It returns datetime value by adding DATE/TIME value ADDTIME(DATETIME, addtime)
CURDATE It returns the current date as string. CURDATE()
CURRENT_DATE It returns the current date as string. CURRENT_DATE()
CURRENT_TIME It returns the current time as string. CURRENT_TIME()
CURRENT_TIMESTAMP It returns the current time and date as string. CURRENT_TIMESTAMP()
CURTIME It returns the current time as string. CURTIME()
DATE It extracts DATE part from the DATETIME. DATE(expression)
DATEDIFF It returns the number of days between two dates. DATEDIFF(date1, date2)
DATE_ADD It adds data/time to the DATE and returns modified date. DATE_ADD(date, INTERVAL value addunit )
DATE_FORMAT It formats date as per specified format. FORMAT(date, format)
DATE_SUB It subtracts date/time interval from specified date and returns modified DATE. DATE_SUB(date, INTERVAL value interval)
DAY It returns DAY of month f the specified DATE. DAY(DATE)
DATNAME It returns week name for the specified DATE. DAYNAME(DATE)
DAYOFMONTH It returns DAY of month f the specified DATE. DAYOFMONTH(DATE)
DAYOFWEEK It returns the week day index like from 1 to 7. DAYOFWEEK(DATE)
DAYOFYEAR It returns the day of year for specified date. DAYOFYEAR(DATE)
EXTRACT It extracts specified part as per our mentioned format from DATE. EXTRACT(part_of_date)
FROMDAYS It returns date from the specified numeric value FROMDAYS(numeric_value)
HOUR It returns hour part from the specified DATE. HOUR(DATETIME)
LAST_DAY It returns last day of month for specified date. LAST_DAY(DATE)
LOCALTIME It returns the current date and time LOCALTIME()
LOCALTIMESTAMP It returns the current date and time LOCALTIME()
MAKEDATE It creates and returns date in result set. MAKEDATE(year, day)
MAKETIME It creates and returns time by using specified values. MAKETIME(HOUR, MINUTE, SECOND)
MICROSECOND It extracts MICROSECOND part from time/datetime value. MICROSECOND(datetime)
MINUTE It extracts MINUTE part from time/datetime value. MINUTE(datetime)
MONTH It extracts MONTH part from time/datetime value. MONTH(datetime)
MONTHNAME It returns MONTH name from time/datetime value. MONTHNAME(datetime)
NOW It returns the current date and time NOW()
PERIOD_ADD It adds specified number of months to period. PERIOD_ADD(period, number)
PERIOD_DIFF It returns the difference between two periods in terms of months. PERIOD_DIFF(period1, period2)
QUARTER It returns the quarter of the year for the given date value as 0 to 4. QUARTER(DATE)
SECOND It extracts SECOND part from time/datetime value. SECOND(datetime)
SEC_TO_TIME It returns time value for specified seconds value. SEC_TO_TIME(seconds)
STR_TO_DATE It returns date value based on string and format. STR_TO_DATE(string, format)
SUBDATE It subtracts date/time interval from date and returns modified date. SUBDATE(date, interval value unit)
SUBTIME It subtracts time interval from date/time and returns modified date. SUBTIME(datetime, time_interval)
SYSDATE It returns current date and time. SYSDATE()
TIME It extracts TIME part from time/datetime value. TIME(datetime)
TIME_FORMAT It formats time by specified time. TIME_FORMAT(time, format)
TIME_TO_SEC It converts time value into seconds. TIME_TO_SEC(time)
TIMEDIFF It returns difference between two date/datetime expressions. TIMEDIFF(time1, time2)
TIMESTAMP It returns the datetime value based on date or datetime value. TIMESTAMP(expression, time)
TO_DAYS It returns number of days between specified date and '0000-00-00'. TO_DAYS(date)
WEEK It returns week number for the specified date. WEEK(date, firstdayofweek)
WEEKDAY It returns weekday number for the specified date. WEEKDAY(date)
WEEKOFYEAR It returns week number i.e. 1 to 53 for the specified date. WEEKOFYEAR(DATE)
YEAR It extracts YEAR part from time/datetime value. YEAR (date)
YEARWEEK It returns year and week number i.e. 1 to 53 for the specified date. YEARWEEK(date)

MySQL Advanced Functions are listed as follows –

Function Description Syntax
BIN It returns binary representation of specified number. BIN(number)
BINARY It converts a value to binary string. BINARY value
CASE It returns multiple outputs for specified conditions. CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
CAST It converts value of any type to specified datatype. CAST(value AS DATATYPE)
COALESCE It returns first non-NULL value in result set. COALESCE(value1, value2,..)
CONNECTION_ID It returns unique connection id for current connection. CONNECTION_ID()
CONV It converts one numeric base system to another. CONV(number, from_base, to_base)
CONVERT It converts value into specified datatype or characterset. CONVERT(value, type)
CURRENT_USER It returns the user name and host name for the MySQL account that server used to authenticate current user. CURRENT_USER()
DATABASE It returns the name of the current database. DATABASE()
IF It returns one value if condition is TRUE, otherwise it returns another condition. IF(condition, value_if_true, value_if_false)
IFNULL It returns a specified value If the expression is NULL. IFNULL(expression, alt_value)
ISNULL It returns 1, If the expression is NULL otherwise it returns 0. ISNULL(expression)
LAST_INSERT_ID It returns auto increment id for the last row that has been updated or inserted. LAST_INSERT_ID(expression)
NULLIF It compares two expressions and returns NULL value if they are same otherwise it returns first expression. NULLIF(expression1, expression2)
SESSION_USER It returns the current name and host name for MySQL connection. SESSION_USER()
SYSTEM_USER It returns the current name and host name for MySQL connection. SYSTEM_USER()
USER It returns the current name and host name for MySQL connection. USER()
VERSION It returns the current version of MySQL database as a string. VERSION()