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() |