MySQL Functions
THE WORLD'S LARGEST WEB DEVELOPER SITE
HTML CSS JAVASCRIPT SQL PHP BOOTSTRAP HOW TO JQUERY W3.CSS ANGULAR XML MORE FORUM EXAMPLES REFERENCES

MySQL Functions

❮ Previous Next ❯

MySQL has many built-in functions.

This reference contains the string, numeric, date, and advanced functions in MySQL.

MySQL String Functions

Function Description
ASCII Returns the number code that represents the specific character
CHAR_LENGTH Returns the length of the specified string (in characters)
CHARACTER_LENGTH Returns the length of the specified string (in characters)
CONCAT Concatenates two or more expressions together
CONCAT_WS Concatenates two or more expressions together and adds a separator between them
FIELD Returns the position of a value in a list of values
FIND_IN_SET Returns the position of a string in a string list
FORMAT Formats a number as a format of "#,###.##", rounding it to a certain number of decimal places
INSERT Inserts a substring into a string at a specified position 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 lower-case
LEFT Extracts a substring from a string (starting from left)
LENGTH Returns the length of the specified string (in bytes)
LOCATE Returns the position of the first occurrence of a substring in a string
LOWER Converts a string to lower-case
LPAD Returns a string that is left-padded with a specified string to a certain length
LTRIM Removes leading spaces from a string
MID Extracts a substring from a string (starting at any position)
POSITION Returns the position of the first occurrence of a substring in a string
REPEAT Repeats a string a specified number of times
REPLACE Replaces all occurrences of a specified string
REVERSE Reverses a string and returns the result
RIGHT Extracts a substring from a string (starting from right)
RPAD Returns a string that is right-padded with a specified string to a certain length
RTRIM Removes trailing spaces from a string
SPACE Returns a string with a specified number of spaces
STRCMP Tests whether two strings are the same
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 the substring of string before number of occurrences of delimiter
TRIM Removes leading and trailing spaces from a string
UCASE Converts a string to upper-case
UPPER Converts a string to upper-case

MySQL Numeric Functions

Function Description
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 a number or the arc tangent of n and m
ATAN2 Returns the arc tangent of n and m
AVG Returns the average value of an expression
CEIL Returns the smallest integer value that is greater than or equal to a number
CEILING Returns the smallest integer value that is greater than or equal to a number
COS Returns the cosine of a number
COT Returns the cotangent of a number
COUNT Returns the number of records in a select query
DEGREES Converts a radian value into degrees
DIV Used for integer division
EXP Returns e raised to the power of number
FLOOR Returns the largest integer value that is less than or equal to a number
GREATEST Returns the greatest value in a list of expressions
LEAST Returns the smallest value in a list of expressions
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 of an expression
MIN Returns the minimum value of an expression
MOD Returns the remainder of n divided by m
PI Returns the value of PI displayed with 6 decimal places
POW Returns m raised to the nth power
POWER Returns m raised to the nth power
RADIANS Converts a value in degrees to radians
RAND Returns a random number or a random number within a range
ROUND Returns a number rounded to a certain number of decimal places
SIGN Returns a value indicating the sign of a number
SIN Returns the sine of a number
SQRT Returns the square root of a number
SUM Returns the summed value of an expression
TAN Returns the tangent of a number
TRUNCATE Returns a number truncated to a certain number of decimal places

MySQL Date Functions

Function Description
ADDDATE Returns a date after a certain time/date interval has been added
ADDTIME Returns a time/datetime after a certain time interval has been added
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 value from a date or datetime expression
DATEDIFF Returns the difference in days between two date values
DATE_ADD Returns a date after a certain time/date interval has been added
DATE_FORMAT Formats a date as specified by a format mask
DATE_SUB Returns a date after a certain time/date interval has been subtracted
DAY Returns the day portion of a date value
DAYNAME Returns the weekday name for a date
DAYOFMONTH Returns the day portion of a date value
DAYOFWEEK Returns the weekday index for a date value
DAYOFYEAR Returns the day of the year for a date value
EXTRACT Extracts parts from a date
FROM_DAYS Returns a date value from a numeric representation of the day
HOUR Returns the hour portion of a date value
LAST_DAY Returns the last day of the month for a given date
LOCALTIME Returns the current date and time
LOCALTIMESTAMP Returns the current date and time
MAKEDATE Returns the date for a certain year and day-of-year value
MAKETIME Returns the time for a certain hour, minute, second combination
MICROSECOND Returns the microsecond portion of a date value
MINUTE Returns the minute portion of a date value
MONTH Returns the month portion of a date value
MONTHNAME Returns the full month name for a date
NOW Returns the current date and time
PERIOD_ADD Takes a period and adds a specified number of months to it
PERIOD_DIFF Returns the difference in months between two periods
QUARTER Returns the quarter portion of a date value
SECOND Returns the second portion of a date value
SEC_TO_TIME Converts numeric seconds into a time value
STR_TO_DATE Takes a string and returns a date specified by a format mask
SUBDATE Returns a date after which a certain time/date interval has been subtracted
SUBTIME Returns a time/datetime value after a certain time interval has been subtracted
SYSDATE Returns the current date and time
TIME Extracts the time value from a time/datetime expression
TIME_FORMAT Formats a time as specified by a format mask
TIME_TO_SEC Converts a time value into numeric seconds
TIMEDIFF Returns the difference between two time/datetime values
TIMESTAMP Converts an expression to a datetime value and if specified adds an optional time interval to the value
TO_DAYS Converts a date into numeric days
WEEK Returns the week portion of a date value
WEEKDAY Returns the weekday index for a date value
WEEKOFYEAR Returns the week of the year for a date value
YEAR Returns the year portion of a date value
YEARWEEK Returns the year and week for a date value

MySQL Advanced Functions

Function Description
BIN Converts a decimal number to a binary number
BINARY Converts a value to a binary string
CASE Lets you evaluate conditions and return a value when the first condition is met
CAST Converts a value from one datatype to another datatype
COALESCE Returns the first non-null expression in a list
CONNECTION_ID Returns the unique connection ID for the current connection
CONV Converts a number from one number base to another
CONVERT Converts a value from one datatype to another, or one character set to another
CURRENT_USER Returns the user name and host name for the MySQL account used by the server to authenticate the current client
DATABASE Returns the name of the default database
IF Returns one value if a condition is TRUE, or another value if a condition is FALSE
IFNULL Lets you to return an alternate value if an expression is NULL
ISNULL Tests whether an expression is NULL
LAST_INSERT_ID Returns the first AUTO_INCREMENT value that was set by the most recent INSERT or UPDATE statement
NULLIF Compares two expressions
SESSION_USER Returns the user name and host name for the current MySQL user
SYSTEM_USER Returns the user name and host name for the current MySQL user
USER Returns the user name and host name for the current MySQL user
VERSION Returns the version of the MySQL database

❮ Previous Next ❯