MySQL Functions
MySQL has many built-in functions. For easy reference, a list of all MySQL functions are provided. These functions can be used in queries in MySQL and can also be used within the programming environment provided by the MySQL database, such as stored procedures, functions, triggers, etc.
Below is the list of MySQL functions, sorted by category:
MySQL String Functions
| Function | Description |
|---|---|
| ASCII() | Returns the ASCII value for the specific character |
| BIT_LENGTH() | Return length of argument in bits |
| CHAR() | Return the character for each integer passed |
| CHAR_LENGTH() | Returns the length of a string (in characters) |
| CHARACTER_LENGTH() | Returns the length of a string (in characters) |
| CONCAT() | Adds two or more expressions together |
| CONCAT_WS() | Adds two or more expressions together with a separator |
| EXPORT_SET() | Returns a string which contains an ON string for every bit set in the value bits, and an OFF string for every unset bit in the value bits. |
| FIELD() | Returns the index position of a value in a list of values |
| FIND_IN_SET() | Returns the position of a string within a list of strings |
| FORMAT() | Formats a number to a format like "#,###,###.##", rounded to a specified number of decimal places |
| INSERT() | Inserts a string within a string at the specified position and 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 number of characters from a string (starting from left) |
| LENGTH() | Returns the length of a 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() | Left-pads a string with another string, to a certain length |
| LTRIM() | Removes leading spaces from a string |
| MAKE_SET() | Return a set of comma-separated strings that have the corresponding bit in bits set |
| MID() | Extracts a substring from a string (starting at any position) |
| OCTET_LENGTH() | Returns the length of a string (in bytes) |
| ORD() | Return character code for leftmost character of the argument |
| POSITION() | Returns the position of the first occurrence of a substring in a string |
| QUOTE() | Escape the argument for use in an SQL statement |
| REPEAT() | Repeats a string as many times as specified |
| REPLACE() | Replaces all occurrences of a substring within a string, with a new substring |
| REVERSE() | Reverses a string and returns the result |
| RIGHT() | Extracts a number of characters from a string (starting from right) |
| RPAD() | Right-pads a string with another string, to a certain length |
| RTRIM() | Removes trailing spaces from a string |
| SPACE() | Returns a string of the specified number of space characters |
| STRCMP() | Compares two strings |
| 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 a substring of a string before a specified number of delimiter occurs |
| TRIM() | Removes leading and trailing spaces from a string |
| UCASE() | Converts a string to upper-case |
| UPPER() | Converts a string to upper-case |
MySQL REGEXP Functions and Operators
| Name | Description |
|---|---|
| NOT REGEXP | Negation of REGEXP |
| NOT RLIKE | Negation of RLIKE |
| REGEXP | Used to check whether string matches regular expression |
| REGEXP_INSTR() | Starting index of substring matching regular expression |
| REGEXP_LIKE() | Whether string matches regular expression |
| REGEXP_REPLACE() | Replace substrings matching regular expression |
| REGEXP_SUBSTR() | Return substring matching regular expression |
| RLIKE | Used to check whether string matches regular expression |
MySQL Math/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 one or two numbers |
| ATAN2() | Returns the arc tangent of two numbers |
| AVG() | Returns the average value of an expression |
| CEIL() | Returns the smallest integer value that is >= to a number |
| CEILING() | Returns the smallest integer value that is >= to a number |
| COS() | Returns the trigonometric cosine of an angle |
| COT() | Returns the trigonometric cotangent of an angle |
| COUNT() | Returns the number of records returned by a select query |
| CRC32() | Computes a cyclic redundancy check value |
| DEGREES() | Converts a value in radians to degrees |
| DIV() | Used for integer division |
| EXP() | Returns e raised to the power of a specified number |
| FLOOR() | Returns the largest integer value that is <= to a number |
| GREATEST() | Returns the greatest value of the list of arguments |
| LEAST() | Returns the smallest value of the list of arguments |
| 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 in a set of values |
| MIN() | Returns the minimum value in a set of values |
| MOD() | Returns the remainder of a number divided by another number |
| PI() | Returns the value of PI |
| POW() | Returns the value of a number raised to the power of another number |
| POWER() | Returns the value of a number raised to the power of another number |
| RADIANS() | Converts a degree value into radians |
| RAND() | Returns a random number |
| ROUND() | Rounds a number to a specified number of decimal places |
| SIGN() | Returns the sign of a number |
| SIN() | Returns the trigonometric sine of an angle |
| SQRT() | Returns the square root of a number |
| SUM() | Calculates the sum of a set of values |
| TAN() | Returns the trigonometric tangent of an angle |
| TRUNCATE() | Truncates a number to the specified number of decimal places |
MySQL Date/Time Functions
| Function | Description |
|---|---|
| ADDDATE() | Adds a time/date interval to a date/datetime and then returns the date/datetime |
| ADDTIME() | Adds a time interval to a time/datetime and then returns the time/datetime value |
| CONVERT_TZ() | Convert a date/datetime value from one time zone to another |
| 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 part from a date/datetime expression |
| DATEDIFF() | Returns the number of days between two date/datetime values |
| DATE_ADD() | Adds a time/date interval to a date and then returns the date |
| DATE_FORMAT() | Formats a date or datetime value by a specified format |
| DATE_SUB() | Subtracts a time/date interval from a date and then returns the date |
| DAY() | Returns the day of the month for a given date/datetime value |
| DAYNAME() | Returns the weekday name for a given date/datetime value |
| DAYOFMONTH() | Returns the day of the month for a given date/datetime value |
| DAYOFWEEK() | Returns the weekday index for a given date/datetime value |
| DAYOFYEAR() | Returns the day of the year for a given date/datetime value |
| EXTRACT() | Extracts a part from a given date |
| FROM_DAYS() | Returns a date from a numeric date value |
| FROM_UNIXTIME() | Returns a date/datetime value from a given Unix timestamp |
| GET_FORMAT() | Returns a format string |
| HOUR() | Returns the hour part for a given date/datetime value |
| LAST_DAY() | Extracts the last day of the month for a given date/datatime value |
| LOCALTIME() | Returns the current date and time |
| LOCALTIMESTAMP() | Returns the current date and time |
| MAKEDATE() | Creates and returns a date based on a year and a number of days value |
| MAKETIME() | Creates and returns a time based on an hour, minute, and second value |
| MICROSECOND() | Returns the microsecond part of a time/datetime value |
| MINUTE() | Returns the minute part of a time/datetime value |
| MONTH() | Returns the month part for a given date/datetime value |
| MONTHNAME() | Returns the name of the month for a given date/datetime value |
| NOW() | Returns the current date and time |
| PERIOD_ADD() | Adds a specified number of months to a period |
| PERIOD_DIFF() | Returns the difference between two periods |
| QUARTER() | Returns the quarter of the year for a given date/datetime value |
| SECOND() | Returns the second part of a time/datetime value |
| SEC_TO_TIME() | Returns a time value based on the specified seconds |
| STR_TO_DATE() | Returns a date based on a string and a format |
| SUBDATE() | Subtracts a time/date interval from a date/datetime and then returns the date/datetime |
| SUBTIME() | Subtracts a time interval from a time/datetime and then returns the time/datetime value |
| SYSDATE() | Returns the current date and time |
| TIME() | Extracts the time part from a given time/datetime value |
| TIME_FORMAT() | Formats a time or datetime value by a specified format |
| TIME_TO_SEC() | Converts a time value into seconds |
| TIMEDIFF() | Returns the difference between two time/datetime expressions |
| TIMESTAMP() | Returns a datetime value based on a date or datetime value |
| TIMESTAMPADD() | Adds an interval to a date/datetime value |
| TIMESTAMPDIFF() | Subtracts an interval from a date/datetime value |
| TO_DAYS() | Returns the number of days between a date/datetime and date "0000-00-00" |
| TO_SECONDS() | Return the date or datetime argument converted to seconds since Year 0 |
| UNIX_TIMESTAMP() | Returns Unix timestamp |
| UTC_DATE() | Returns the current UTC date |
| UTC_TIME() | Returns the current UTC time |
| UTC_TIMESTAMP() | Returns the current UTC date and time |
| WEEK() | Returns the week number for a given date/datetime value |
| WEEKDAY() | Returns the weekday number for a given date/datetime value |
| WEEKOFYEAR() | Returns the week number for a given date/datetime value |
| YEAR() | Returns the year part for a given date/datetime value |
| YEARWEEK() | Returns the year and week number for a given date/datetime value |
MySQL Advanced Functions
| Function | Description |
|---|---|
| BIN() | Returns a binary representation of a number |
| BINARY() | Converts a value to a binary string |
| CASE() | Goes through conditions and return a value when the first condition is met |
| CAST() | Converts a value (of any type) into a specified datatype |
| COALESCE() | Returns the first non-null value in a list |
| CONNECTION_ID() | Returns the unique connection ID for the current connection |
| CONV() | Converts a number from one numeric base system to another |
| CONVERT() | Converts a value into the specified datatype or character set |
| CURRENT_USER() | Returns the user name and host name for the MySQL account that the server used to authenticate the current client |
| DATABASE() | Returns the name of the current database |
| ELT() | Returns string at specified index number |
| FROM_BASE64() | Decode base-64 encoded string and return result |
| HEX() | Returns a string containing hexadecimal representation of decimal or string value |
| IF() | Returns a value if a condition is TRUE, or another value if a condition is FALSE |
| IFNULL() | Return a specified value if the expression is NULL, otherwise return the expression. |
| ISNULL() | Returns 1 or 0 depending on whether an expression is NULL |
| LAST_INSERT_ID() | Returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table |
| NULLIF() | Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned |
| OCT() | Returns a string containing octal representation of a number |
| SESSION_USER() | Returns the current MySQL user name and host name |
| SOUNDEX() | Returns a soundex string |
| SOUNDS LIKE | Compare sounds |
| SYSTEM_USER() | Returns the current MySQL user name and host name |
| TO_BASE64() | Return the argument converted to a base-64 string |
| UNHEX() | Return a string containing hex representation of a number |
| USER() | Returns the current MySQL user name and host name |
| VERSION() | Returns the current version of the MySQL database |
MySQL Encryption Functions
| Function | Description |
|---|---|
| ENCRYPT() | Encrypts a string using UNIX crypt() |
| MD5() | Returns an MD5 128-bit checksum representation of a string |
| OLD_PASSWORD() | Used by the authentication system in MySQL to generate a hashed password from a plaintext password string |
| PASSWORD() | Used by the authentication system in MySQL to generate a hashed password from a plaintext password string using more powerful hashing techniques |
| SHA() | Returns an SHA-1 160-bit checksum representation of a string |
| SHA1() | Returns an SHA-1 160-bit checksum representation of a string |
| SHA2() | Calculates the SHA-2 family of hash functions |
