Built-in Functions

You can use most of the functions that MySQL makes available for use in SQL statements within stored programs. These are fully documented in the MySQL reference manual, and we provide details and examples for most of these functions in Chapter 9. We'll also talk about how you can create your own "stored" functions in the MySQL stored program language in Chapter 10.

The functions that may be used in SQL but not in stored programs are those involved in group (multiple-row) operators. These include functions such as SUM, COUNT, MIN, MAX, and AVG. MySQL accepts these functions within expressions, but they will return NULL as shown in Example 3-13.

Example 3-13. Aggregate functions in stored procedures return NULL

mysql> create procedure functions( )
begin
 DECLARE a int default 2;
 declare b int default 3;
 declare c FLOAT;

 SET c=SUM(a); select c;

end;

Query OK, 0 rows affected (0.00 sec)

mysql> call functions( );

+------+
| c |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

MySQL functions fall into the following categories:

 

String functions

These functions perform operations on string variables. For example, you can concatenate strings, find characters within strings, obtain a substring, and perform other common operations.

 

Mathematical functions

These functions perform operations on numbers. For example, you can perform exponentiation (raise to a power), trigonometric functions (sine, cosine, etc.), random number functions, logarithms, and so on.

 

Date and time functions

These functions perform operations on dates and times. For example, you can get the current date, add or subtract time intervals from dates, find the difference between two dates, and extract certain portions of a date (e.g., get the time of day from a date-time).

 

Miscellaneous functions

These functions include everything not easily categorized in the above three groupings. They include cast functions, flow control functions (e.g., CASE), informational functions (e.g., server version), and encryption functions.

Table 3-8 summarizes some of the most frequently used functions; see Chapter 9 for a more complete coverage of function syntax and examples.

Table 3-8. Commonly used MySQL functions

Function	Description
ABS(number)	Returns the absolute value of the number supplied. For instance, ABS(-2.3)=2.3.
CEILING(number)	Returns the next highest integer. For instance, CEILING(2.3)=3.
CONCAT(string1[,string2,string3,...])	Returns a string comprised of all the supplied strings joined (concatenated) together.
CURDATE	Returns the current date (without the time portion).
DATE_ADD(date,INTERVAL amount_type)	Adds the specified interval to the specified date and returns a new date. Valid types include SECOND, MINUTE, HOUR, DAY, MONTH, and YEAR.
DATE_SUB(date,INTERVAL interval_type)	Subtracts the specified interval from the specified date and returns a new date. Valid types include SECOND, MINUTE, HOUR, DAY, MONTH, and YEAR.
FORMAT(number,decimals)	Returns a number with a specified number of decimal places and with 1000 separators (usually ",").
GREATEST(num1,num2[,num3, ... ])	Returns the greatest number from all the numbers supplied as arguments.
IF(test, value1,value2)	Tests a logical condition. If TRUE, returns value1; otherwise, returns value2.
IFNULL(value,value2)	Returns the value of the first argument, unless that argument is NULL; in that case, it returns the value of the second argument.
INSERT(string,position,length,new)	Inserts a string into the middle of another string.
INSTR(string,substring)	Finds the location of a substring within a string.
ISNULL(expression)	Returns 1 if the argument is NULL, 0 otherwise.
LEAST(num1,num2[,num3, ... ])	Returns the smallest number from the list of arguments.
LEFT(string,length)	Returns the leftmost portion of a string.
LENGTH(string)	Returns the length of a string in bytes. CHAR_LENGTH can be used if you want to return the number of characters (which could be different if you are using a multibyte character set).
LOCATE(substring,string[,number])	Returns the location of the substring within the string, optionally starting the search at the position given by the third argument.
LOWER(string)	Translates the given string into lowercase.
LPAD(string,length,padding)	Left-pads the string to the given length, using the third argument as the pad character.
LTRIM(string)	Removes all leading whitespace from a string.
MOD(num1,num2)	Returns the modulo (remainder) returned by the division of the first number by the second number.
NOW	Returns the current date and time.
POWER(num1,num2)	Raises num1 to the power num2.
RAND([seed])	Returns a random number. The seed may be used to initialize the random number generator.
REPEAT(string,number)	Returns a string consisting of number repetitions of the given string.
REPLACE(string,old,new)	Replaces all occurrences of old with new in the given string.
ROUND(number[,decimal])	Rounds a numeric value to the specified number of decimal places.
RPAD(string,length,padding)	Right-pads string to the specified length using the specified padding character.
RTRIM(string)	Removes all trailing blanks from string.
SIGN(number)	Returns -1 if the number is less than 0, 1 if the number is greater than 0, or 0 if the number is equal to 0.
SQRT(number)	Returns the square root of the given number.
STRCMP(string1,string2)	Returns 0 if the two strings are identical, -1 if the first string would sort earlier than the second string, or 1 otherwise.
SUBSTRING(string,position,length)	Extracts length characters from string starting at the specified position.
UPPER(string)	Returns the specified string converted to uppercase.
VERSION	Returns a string containing version information for the current MySQL server.

Functions can be used in any statement that accepts an expressionfor example, in SET statements, conditional statements (IF, CASE), and loop control clauses. Example 3-14 shows some examples that use functions in SET and IF clauses.

Example 3-14. Examples of functions in SET and IF clauses

CREATE PROCEDURE function_example( )
BEGIN

 DECLARE TwentyYearsAgoToday DATE;
 DECLARE mystring VARCHAR(250);

 SET TwentyYearsAgoToday=date_sub(curdate( ), interval 20 year);

 SET mystring=concat('It was ',TwentyYearsAgoToday,
 ' Sgt Pepper taught the band to play...');

 SELECT mystring;

 IF (CAST(SUBSTR(version( ),1,3) AS DECIMAL(2,1)) <5.0) THEN
 SELECT 'MySQL versions earlier than 5.0 cannot run stored programs - you
 must be hallucinating';
 ELSE
 SELECT 'Thank goodness you are running 5.0 or higher!';
 END IF;

END$$

CALL function_example( )$$

+---------------------------------------------------------+
| mystring |
+---------------------------------------------------------+
| It was 1985-11-22 Sgt Pepper taught the band to play... |
+---------------------------------------------------------+
1 row in set (0.03 sec)

+-----------------------------------------------+
| Thank goodness you are running 5.0 or higher! |
+-----------------------------------------------+
| Thank goodness you are running 5.0 or higher! |
+-----------------------------------------------+
1 row in set (0.03 sec)