THE WORLD'S LARGEST WEB DEVELOPER SITE

MySQL Functions


MySQL has many built-in functions.


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 comma-delimited string list
FORMAT Formats a number as a format of "#,###.##"
INSERT Inserts a substring into a string at a specified position for a certain number of characters
INSTR Returns the location of a substring in a 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 location of the first appearance 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 location of a substring in a string
REPEAT Repeats a string a specified number of times
REPLACE Replaces all occurrences of a specified string
REVERSE Returns a string with the characters in reverse order
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 Returns an integer value representing the result of a string comparison
SUBSTR Extracts a substring from a string
SUBSTRING Extracts a substring from a string
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 returns 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 count of an expression
DEGREES Converts a radian value into degrees
DIV Used for integer division where n is divided by m and an integer value is returned
EXP Returns e raised to the power of number
FLOOR Returns the largest integer value that is equal to or less than 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 which a certain time/date interval has been added
ADDTIME Returns a time/datetime value after which 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 a date after which a certain time/date interval has been added
DATE_ADD Formats a date as specified by a format mask
DATE_FORMAT Returns a date after which a certain time/date interval has been subtracted
DATE_SUB Returns the difference in days between two date values
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 day of the year for a date value
EXTRACT Extracts parts from a date
FROM_DAYS Takes a numeric representation of the day and returns a date value
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 (formatted as YYMM or YYYYMM) and adds a specified number of months to it
PERIOD_DIFF Returns the difference in months between two periods (formatted as YYMM or YYYYMM)
QUARTER Returns the quarter portion of a date value
SECOND Converts numeric seconds into a time value
SEC_TO_TIME Returns the second portion of a date 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 which 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 (expressed as a time value) 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
WEEKOFYEAR Returns 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