MySQL Functions

MySQL offers a comprehensive set of built-in functions to perform various operations on data. These functions cover string manipulation, numeric calculations, date and time adjustments, and more advanced functionalities.

MySQL String Functions

Function	Description
`ASCII(str)`	Returns the ASCII value of the first character in the string.
`CHAR_LENGTH(str)`/`CHARACTER_LENGTH(str)`	Returns the length of a string (in characters).
`CONCAT(str1,str2,...)`	Concatenates (joins) two or more strings.
`CONCAT_WS(separator,str1,str2,...)`	Concatenates strings with a separator between them.
`FIELD(str,str1,str2,...)`	Returns the position of `str` in the list of strings.
`FIND_IN_SET(str,strlist)`	Returns the position of `str` in the comma-separated list `strlist`.
`FORMAT(number,format)`	Formats a number with thousands separators and decimal places.
`INSERT(str,pos,len,newstr)`	Inserts `newstr` into `str` at position `pos` for length `len`.
`INSTR(str,substr)`	Returns the starting position of `substr` within `str`.
`LCASE(str)`/`LOWER(str)`	Converts a string to lowercase.
`LEFT(str,len)`	Returns the leftmost `len` characters of `str`.
`LENGTH(str)`	Returns the length of a string (in bytes).
`LOCATE(substr,str)`	Returns the starting position of `substr` within `str`.
`LPAD(str,len,padstr)`	Pads `str` on the left with `padstr` to length `len`.
`LTRIM(str)`	Removes leading spaces from `str`.
`MID(str,pos,len)`/`SUBSTR(str,pos,len)`/`SUBSTRING(str,pos,len)`	Extracts a substring of length `len` from `str`, starting at position `pos`.
`POSITION(substr IN str)`	Returns the starting position of `substr` within `str`.
`REPEAT(str,count)`	Repeats `str` `count` times.
`REPLACE(str,from,to)`	Replaces all occurrences of `from` with `to` in `str`.
`REVERSE(str)`	Reverses a string.
`RIGHT(str,len)`	Returns the rightmost `len` characters of `str`.
`RPAD(str,len,padstr)`	Pads `str` on the right with `padstr` to length `len`.
`RTRIM(str)`	Removes trailing spaces from `str`.
`SPACE(n)`	Returns a string containing `n` spaces.
`STRCMP(str1,str2)`	Compares two strings.
`SUBSTRING_INDEX(str,delim,count)`	Returns the substring of `str` before the `count`th occurrence of `delim`.
`TRIM([{BOTH\|LEADING\|TRAILING} [remstr] FROM] str)`	Removes leading and/or trailing spaces or characters from a string.
`UCASE(str)`/`UPPER(str)`	Converts a string to uppercase.

MySQL Numeric Functions

Function	Description
`ABS(number)`	Returns the absolute value of a number.
`ACOS(number)`	Returns the arc cosine of a number.
`ASIN(number)`	Returns the arc sine of a number.
`ATAN(number)`/`ATAN2(y,x)`	Returns the arc tangent of a number or two numbers.
`AVG(expression)`	Returns the average value of an expression.
`CEIL(number)`/`CEILING(number)`	Returns the smallest integer greater than or equal to a number.
`COS(number)`	Returns the cosine of a number.
`COT(number)`	Returns the cotangent of a number.
`COUNT(*)`	Counts rows in a table or result set.
`DEGREES(number)`	Converts radians to degrees.
`DIV(n1,n2)`	Performs integer division.
`EXP(number)`	Returns e (Euler's number) raised to the power of a number.
`FLOOR(number)`	Returns the largest integer less than or equal to a number.
`GREATEST(value1,value2,...)`	Returns the largest value in a list.
`LEAST(value1,value2,...)`	Returns the smallest value in a list.
`LN(number)`	Returns the natural logarithm of a number.
`LOG(number)`/`LOG(number,base)`	Returns the natural logarithm or logarithm to a specified base.
`LOG10(number)`	Returns the base-10 logarithm of a number.
`LOG2(number)`	Returns the base-2 logarithm of a number.
`MAX(expression)`	Returns the maximum value of an expression.
`MIN(expression)`	Returns the minimum value of an expression.
`MOD(n1,n2)`	Returns the remainder of `n1` divided by `n2`.
`PI()`	Returns the value of PI.
`POW(number,power)`/`POWER(number,power)`	Raises a number to a power.
`RADIANS(number)`	Converts degrees to radians.
`RAND()`/`RAND(seed)`	Returns a random number.
`ROUND(number,decimals)`	Rounds a number to a specified number of decimal places.
`SIGN(number)`	Returns the sign of a number (-1, 0, or 1).
`SIN(number)`	Returns the sine of a number.
`SQRT(number)`	Returns the square root of a number.
`SUM(expression)`	Returns the sum of an expression.
`TAN(number)`	Returns the tangent of a number.
`TRUNCATE(number,decimals)`	Truncates a number to a specified number of decimal places.

MySQL Date Functions

Function	Description
`ADDDATE(date,INTERVAL expr unit)`/`DATE_ADD(date,INTERVAL expr unit)`	Adds a time/date interval to a date.
`ADDTIME(time,expr)`	Adds a time interval to a time/datetime value.
`CURDATE()`/`CURRENT_DATE()`	Returns the current date.
`CURRENT_TIME()`	Returns the current time.
`CURRENT_TIMESTAMP()`/`LOCALTIME()`/`LOCALTIMESTAMP()`	Returns the current timestamp.
`CURTIME()`	Returns the current time.
`DATE(datetime)`	Extracts the date part from a datetime value.
`DATEDIFF(unit,datetime1,datetime2)`	Returns the difference between two dates.
`DATE_FORMAT(datetime,format)`	Formats a date value according to a format string.
`DATE_SUB(date,INTERVAL expr unit)`	Subtracts a time/date interval from a date.
`DAY(date)`/`DAYOFMONTH(date)`	Returns the day of the month.
`DAYNAME(date)`	Returns the weekday name.
`DAYOFWEEK(date)`	Returns the weekday index (1-7, Sunday=1).
`DAYOFYEAR(date)`	Returns the day of the year (1-366).
`EXTRACT(unit FROM date)`	Extracts a part from a date (year, month, day, etc.).
`FROM_DAYS(n)`	Returns a date given the number of days since year 0.
`HOUR(time)`	Returns the hour part of a time value.
`LAST_DAY(date)`	Returns the last day of the month for the given date.
`MAKEDATE(year,dayofyear)`	Creates a date from year and day-of-year.
`MAKETIME(hour,minute,second)`	Creates a time from hour, minute, and second values.
`MICROSECOND(datetime)`	Returns the microseconds part of a datetime value.
`MINUTE(time)`	Returns the minute part of a time value.
`MONTH(date)`	Returns the month (1-12).
`MONTHNAME(date)`	Returns the full month name.
`NOW()`	Returns the current date and time.
`PERIOD_ADD(P,N)`	Adds `N` months to period `P` (format YYMM).
`PERIOD_DIFF(P1,P2)`	Returns the difference in months between two periods.
`QUARTER(date)`	Returns the quarter (1-4) of the year.
`SECOND(time)`	Returns the seconds part of a time value.
`SEC_TO_TIME(seconds)`	Converts seconds to a time value.
`STR_TO_DATE(str,format)`	Converts a string to a date.
`SUBDATE(date,INTERVAL expr unit)`	Subtracts a time/date interval from a date.
`SUBTIME(time,expr)`	Subtracts a time interval from a time/datetime value.
`SYSDATE()`	Returns the current date and time.
`TIME(datetime)`	Extracts the time part from a datetime value.
`TIME_FORMAT(time,format)`	Formats a time value.
`TIME_TO_SEC(time)`	Converts a time value into seconds.
`TIMEDIFF(time1,time2)`	Returns the difference between two times.
`TIMESTAMP(datetime)`	Returns a datetime value.
`TO_DAYS(date)`	Returns the number of days since year 0.
`WEEK(date)`/`WEEKOFYEAR(date)`	Returns the week number.
`WEEKDAY(date)`	Returns the weekday number (0-6, Sunday=0).
`YEAR(date)`	Returns the year.
`YEARWEEK(date)`	Returns the year and week number.

MySQL Advanced Functions

Function	Description
`BIN(number)`	Returns a binary representation of a number.
`BINARY(str)`	Converts a string to a binary string.
`CASE WHEN condition THEN result ELSE result END`	Performs conditional logic.
`CAST(value AS type)`/`CONVERT(value,type)`	Converts a value to a specified data type.
`COALESCE(value1,value2,...)`	Returns the first non-NULL value in a list.
`CONNECTION_ID()`	Returns the connection ID.
`CONV(number,from_base,to_base)`	Converts a number between bases.
`CURRENT_USER()`/`SESSION_USER()`/`SYSTEM_USER()`/`USER()`	Returns the current user information.
`DATABASE()`	Returns the current database name.
`IF(condition,value_if_true,value_if_false)`	Performs conditional logic.
`IFNULL(expr1,expr2)`	Returns `expr1` if not NULL, otherwise `expr2`.
`ISNULL(expr)`	Returns 1 if `expr` is NULL, 0 otherwise.
`LAST_INSERT_ID()`	Returns the last AUTO_INCREMENT value generated.
`NULLIF(expr1,expr2)`	Returns NULL if `expr1` equals `expr2`, otherwise `expr1`.

MySQL Functions

MySQL String Functions

MySQL Numeric Functions

MySQL Date Functions

MySQL Advanced Functions

MySQL VERSION() Function

VERSION(): Definition and Usage

Syntax

Syntax

Example

Retrieving the MySQL Version

Syntax

Output