MySQL Functions

MySQL String Functions

MySQL includes numerous built-in functions, encompassing string, numeric, date, and various advanced functions.

This reference covers them all.

Function	Description
ASCII	Returns the ASCII value of a given character
CHAR LENGTH	Returns the number of characters in a string
CHARACTER LENGTH	Returns the number of characters in a string
CONCAT	Combines two or more expressions by addition
CONCAT WS	Concatenates two or more expressions with a separator
FIELD	Returns the position of a value within a list
FIND IN SET	Returns the index of a string within a list of strings
FORMAT	Formats a number with commas as thousands separators and rounds it to a specified number of decimal places, like “#,###,###.##”
INSERT	Inserts a substring into a string at a specified position, replacing a certain number of characters
INSTR	Returns the index of the first occurrence of a substring within another string
LCASE	Converts a string to lowercase
LEFT	Extracts a specified number of characters from the beginning of a string
LENGTH	Returns the byte length of a string
LOCATE	Returns the index of the first occurrence of a substring within a string
LOWER	Changes a string to lowercase
LPAD	Pads a string with another string on the left side to reach a specified length
LTRIM	Trims leading spaces from a string
MID	Extracts a portion of a string, starting from any position
POSITION	Returns the index of the first occurrence of a substring within a string
REPEAT	Repeats a string a specified number of times
REPLACE	Replaces all instances of a substring within a string with a new substring
REVERSE	Returns the reversed version of a string
RIGHT	Extracts a specified number of characters from the end of a string
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 Numeric Functions

Function	Description
ABS	Returns the magnitude of a number.
ACOS	Returns the inverse cosine (arc cosine) of a number.
ASIN	Returns the inverse sine (arc sine) of a number
ATAN	Returns the inverse tangent (arc tangent) of one or two numbers.
ATAN2	Returns the inverse tangent (arc tangent) of two numbers
AVG	Calculates the mean value of an expression.
CEIL	Returns the smallest integer value that is greater than or equal to a number.
CEILING	Returns the smallest integer greater than or equal to a given number.
COS	Returns the cosine value of a number.
COT	Returns the cotangent value of a number.
COUNT	Returns the count of records retrieved by a select query.
DEGREES	Converts a value from radians to degrees
DIV	Used for performing integer division.
EXP	Returns the exponential value of a specified number.
FLOOR	Returns the largest integer value that is less than or equal to a number.
GREATEST	Returns the maximum value from a list of arguments.
LEAST	Returns the minimum value from a list of arguments.
LN	Returns the logarithm of a number to the base e (natural logarithm).
LOG	Returns either the natural logarithm of a number or the logarithm of a number to a specified base.
LOG10	Returns the base-10 logarithm (common logarithm) of a number.
LOG2	Returns the base-2 logarithm of a number
MAX	Returns the highest value among a set of values.
MIN	Returns the lowest value among a set of values.
MOD	Returns the remainder when one number is divided by another.
PI	Returns the numerical value of the mathematical constant π (pi).
POW	Returns the result of raising a number to the power of another number.
POWER	Returns the result of exponentiating a number by another number.
RADIANS	Converts a degree measurement into radians.
RAND	Generates a random number.
ROUND	Rounds a number to a specified decimal precision.
SIGN	Determines the sign of a number.
SIN	Returns the sine value of a number.
SQRT	Returns the square root value of a number
SUM	Returns the total sum of a set of values.
TAN	Returns the tangent value of a number.
TRUNCATE	Limits a number to the specified number of decimal places.

MySQL Date Functions

Function	Description
ADDDATE	Calculates a new date by adding a specified time or date interval to an existing date.
ADDTIME	Calculates a new date by adding a specified time or date interval to an existing date.
CURDATE	Provides the current date.
CURRENT DATE	Provides the current date.
CURRENT TIME	Provides the current time.
CURRENT TIMESTAMP	Provides the current date and time.
CURTIME	Provides the current time.
DATE	Retrieves the date component from a datetime expression.
DATEDIFF	Calculates the number of days between two date values.
DATE ADD	Adds a time or date interval to a date and returns the resulting date.
DATE FORMAT	Specifies the format of a date.
DATE SUB	Subtracts a time or date interval from a date and returns the resulting date.
DAY	Returns the day of the month from a given date.
DAYNAME	Returns the name of the weekday for a given date.
DAYOFMONTH	Returns the day of the month from a specified date.
DAYOFWEEK	Returns the index of the weekday for a given date.
DAYOFYEAR	Returns the day of the year from a given date.
EXTRACT	Extracts a specific part from a given date.
FROM DAYS	Converts a numeric date value into a date.
HOUR	Returns the hour component from a given date.
LAST DAY	Determines the last day of the month from a given date.
LOCALTIME	Provides the current date and time.
LOCALTIMESTAMP	Provides the current date and time.
MAKEDATE	Generates and returns a date based on a given year and a specified number of days
MAKETIME	Constructs and returns a time based on specified hour, minute, and second values.
MICROSECOND	Returns the microsecond component of a time or datetime.
MINUTE	Returns the minute component of a time or datetime.
MONTH	Returns the month component from a given date.
MONTHNAME	Returns the name of the month for a specified date.
NOW	Provides the current date and time.
PERIOD ADD	Increases a specified period by a certain number of months.
PERIOD DIFF	Calculates the difference between two periods.
QUARTER	Returns the quarter of the year from a given date.
SECOND	Returns the seconds component of a time or datetime.
SEC TO TIME	Returns a time value corresponding to the specified number of seconds.
STR TO DATE	Returns a date parsed from a string using a specified format.
SUBDATE	Subtracts a specified time or date interval from a date and returns the resulting date.
SUBTIME	Subtracts a specified time interval from a datetime and returns the resulting time or datetime.
SYSDATE	Provides the current date and time.
TIME	Extracts the time component from a given time or datetime.
TIME FORMAT	Formats a time according to a specified format.
TIME TO SEC	Converts a time value to seconds.
TIMEDIFF	Calculates the difference between two time or datetime expressions.
TIMESTAMP	Returns a datetime value derived from a date or datetime value.
TO DAYS	Calculates the number of days between a date and the date ‘0000-00-00’.”
WEEK	Returns the week number for a specified date.
WEEKDAY	Returns the weekday number for a specified date.
WEEKOFYEAR	Returns the week number of the year for a specified date.
YEAR	Returns the year component from a given date.
YEARWEEK	Returns the year and week number for a specified date.

MySQL Advanced Functions

Function	Description
BIN	Returns the binary form of a given number.
BINARY	Converts a value into its binary string representation.
CASE	Processes a series of conditions and returns a value upon encountering the first condition that is met.
CAST	Converts a value into a specified data type, regardless of its original type.
COALESCE	Returns the first value in a list that is not null.
CONNECTION ID	Returns the unique identifier associated with the current connection.
CONV	Converts a number from one numerical base system to another.
CONVERT	Converts a value into the specified data type or character set.
CURRENT USER	Returns the user name and host name associated with the MySQL account used by the server to authenticate the current client.
DATABASE	Returns the name of the database currently in use.
IF	Returns one value if a condition is true, and another value if the condition is false.
IFNULL	Returns a specified value if an expression is null; otherwise, it returns the expression itself.
ISNULL	Returns 1 if an expression is NULL, otherwise returns 0.
LAST INSERT ID	Returns the AUTO_INCREMENT ID of the most recent row that was inserted or updated in a table.
NULLIF	Compares two expressions; if they are equal, it returns NULL. Otherwise, it returns the first expression.
SESSION USER	Returns the MySQL user name and host name currently authenticated for the session.
SYSTEM USER	Returns the user name and host name of the current MySQL session’s authenticated user.
USER	Returns the MySQL user name and host name associated with the current session.
VERSON	Returns the current version of the MySQL database system.