10.6. Functions in SQL Expressions

This section describes the categories of functions that are available in MySQL and provides examples that show how to use several of them. (Some of the constructs mentioned here really are operators, even though the section titles all say "functions.")

Note

Many functions are available in MySQL. The following sections demonstrate some representative examples, but those shown make up only a fraction of the number available. Consult the functions chapter in the MySQL Reference Manual for a complete list of functions and how to use them. In studying for the exam, you should familiarize yourself with all the SQL functions listed in that chapter. You're not expected to know every little detail about each one, but you'll be expected to know their general behavior.

Functions can be invoked within expressions and return a value that is used in place of the function call when the expression is evaluated. When you invoke a function, there must be no space after the function name and before the opening parenthesis. It's possible to change this default behavior by enabling the IGNORE_SPACE SQL mode to cause spaces after the function name to be ignored:

 mysql> SELECT PI (); ERROR 1305 (42000): FUNCTION world.PI does not exist mysql> SET sql_mode = 'IGNORE_SPACE'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT PI (); +----------+ | PI ()    | +----------+ | 3.141593 | +----------+ 1 row in set (0.00 sec)

10.6.1. Comparison Functions

Comparison functions enable you to test relative values or membership of one value within a set of values.

LEAST() and GREATEST() take a set of values as arguments and return the one that is smallest or largest, respectively:

 mysql> SELECT LEAST(4,3,8,-1,5), LEAST('cdef','ab','ghi'); +-------------------+--------------------------+ | LEAST(4,3,8,-1,5) | LEAST('cdef','ab','ghi') | +-------------------+--------------------------+ |                -1 | ab                       | +-------------------+--------------------------+ mysql> SELECT GREATEST(4,3,8,-1,5), GREATEST('cdef','ab','ghi'); +----------------------+-----------------------------+ | GREATEST(4,3,8,-1,5) | GREATEST('cdef','ab','ghi') | +----------------------+-----------------------------+ |                    8 | ghi                         | +----------------------+-----------------------------+

INTERVAL() takes a comparison value as its first argument. The remaining arguments should be a set of values in sorted order. INTERVAL() compares the first argument to the others and returns a value to indicate how many of them are less than or equal to it.

 mysql> SELECT INTERVAL(2,1,2,3,4); +---------------------+ | INTERVAL(2,1,2,3,4) | +---------------------+ |                   2 | +---------------------+ mysql> SELECT INTERVAL(0,1,2,3,4); +---------------------+ | INTERVAL(0,1,2,3,4) | +---------------------+ |                   0 | +---------------------+ mysql> SELECT INTERVAL(6.3,2,4,6,8,10); +--------------------------+ | INTERVAL(6.3,2,4,6,8,10) | +--------------------------+ |                        3 | +--------------------------+

It's sometimes necessary to determine whether a value is equal to any of several specific values. One way to accomplish this is to combine several equality tests into a single expression with the OR logical operator:

 ... WHERE id = 13 OR id = 45 OR id = 97 OR id = 142 ... WHERE name = 'Tom' OR name = 'Dick' OR name = 'Harry'

However, MySQL provides an IN() operator that performs the same kind of comparison and that is more concise and easier to read. To use it, provide the comparison values as a comma-separated list of arguments to IN():

 ... WHERE id IN(13,45,97,142) ... WHERE name IN('Tom','Dick','Harry')

Using IN() is equivalent to writing a list of comparisons with OR, but IN() is much more efficient.

Arguments to IN() may be of any type (numeric, string, or temporal), although generally all values given within a list should all have the same type.

Elements in a list may be given as expressions that are evaluated to produce a value. If the expression references a column name, the column is evaluated for each row.

IN() always returns NULL when used to test NULL. That is, NULL IN(list) is NULL for any list of values, even if NULL is included in the list. This occurs because NULL IN(NULL) is equivalent to NULL = NULL, which evaluates to NULL.

IN() tests membership within a set of individual values. If you're searching for a range of values, a range test might be more suitable. The BETWEEN operator takes the two endpoint values of the range and returns true if a comparison value lies between them:

 ... WHERE id BETWEEN 5 AND 10

The comparison is inclusive, so the preceding expression is equivalent to this one:

 ... WHERE id >= 5 AND id <= 10

10.6.2. Control Flow Functions

Control flow functions enable you to choose between different values based on the result of an expression. IF() tests the expression in its first argument and returns its second or third argument depending on whether the expression is true or false:

 mysql> SELECT IF(1 > 0, 'yes','no'); +-----------------------+ | IF(1 > 0, 'yes','no') | +-----------------------+ | yes                   | +-----------------------+

The CASE construct is not a function, but it too provides flow control. It has two forms of syntax. The first looks like this:

 CASE case_expr   WHEN when_expr THEN result   [WHEN when_expr THEN result] ...   [ELSE result] END

The expression case_expr is evaluated and used to determine which of the following clauses in the rest of the CASE to execute. The when_expr in the initial WHEN clause is evaluated and compared to case_expr. If the two are equal, the expression following THEN is the result of the CASE. If when_expr is not equal to case_expr, and there are any following WHEN clauses, they are handled similarly in turn. If no WHEN clause has a when_expr equal to case_expr, and there is an ELSE clause, the expression in the ELSE clause becomes the CASE result. If there is no ELSE clause the result is NULL.

The following CASE expression returns a string that indicates whether the value of the @val user variable is 0, 1, or something else:

 mysql> SET @val = 1; mysql> SELECT CASE @val     ->   WHEN 0 THEN '@val is 0'     ->   WHEN 1 THEN '@val is 1'     ->   ELSE '@val is not 0 or 1'     -> END AS result; +-----------+ | result    | +-----------+ | @val is 1 | +-----------+

The second CASE syntax looks like this:

 CASE   WHEN when_expr THEN result   [WHEN when_expr THEN result] ...   [ELSE result] END

For this syntax, the conditional expression in each WHEN clause is executed until one is found to be true, and then its corresponding THEN expression becomes the result of the CASE. If none of them are true and there is an ELSE clause, its expression becomes the CASE result. If there is no ELSE clause the result is NULL.

The following CASE expression tests whether the value of the @val user variable is NULL or less than, greater than, or equal to 0:

 mysql> SET @val = NULL; mysql> SELECT CASE     ->   WHEN @val IS NULL THEN '@val is NULL'     ->   WHEN @val < 0 THEN '@val is less than 0'     ->   WHEN @val > 0 THEN '@val is greater than 0'     ->   ELSE '@val is 0'     -> END AS result; +--------------+ | result       | +--------------+ | @val is NULL | +--------------+

Note that IF() and CASE as used in expressions have somewhat different syntax than the IF and CASE statements that can be used within compound statements (the statements end with END CASE, not just END). For the syntax of the latter, see Section 18.5.8, "Flow Control." That section also contains some discussion about the kinds of test for which each type of CASE statement syntax are appropriate; the same remarks apply to CASE expressions.

10.6.3. Aggregate Functions

Aggregate functions perform summary operations on a set of values, such as counting, averaging, or finding minimum or maximum values. Aggregate functions often are used in conjunction with a GROUP BY clause to arrange values from a result set into groups. In this case, the aggregate function produces a summary value for each group. The use of aggregate functions in MySQL is covered in Section 9.4, "Aggregating Results."

10.6.4. Mathematical Functions

Numeric functions perform several types of operations, such as rounding, truncation, trigonometric calculations, or generating random numbers.

The ROUND() function performs rounding of its argument. The rounding method applied to the fractional part of a number depends on whether the number is an exact or approximate value:

For positive exact values, ROUND() rounds up to the next integer if the fractional part is .5 or greater, and down to the next integer otherwise. For negative exact values, ROUND() rounds down to the next integer if the fractional part is .5 or greater, and up to the next integer otherwise. Another way to state this is that a fraction of .5 or greater rounds away from zero and a fraction less than .5 rounds toward zero:
```
 mysql> SELECT ROUND(28.5), ROUND(-28.5); +-------------+--------------+ | ROUND(28.5) | ROUND(-28.5) | +-------------+--------------+ | 29          | -29          | +-------------+--------------+ 
```

For approximate values, ROUND() uses the rounding method provided in the C library used by the MySQL server. This can vary from system to system, but typically rounds to the nearest even integer:

 mysql> SELECT ROUND(2.85E1), ROUND(-2.85E1); +---------------+----------------+ | ROUND(2.85E1) | ROUND(-2.85E1) | +---------------+----------------+ |            28 |            -28 | +---------------+----------------+

FLOOR() returns the largest integer not greater than its argument, and CEILING() returns the smallest integer not less than its argument:

 mysql> SELECT FLOOR(-14.7), FLOOR(14.7); +--------------+-------------+ | FLOOR(-14.7) | FLOOR(14.7) | +--------------+-------------+ |          -15 |          14 | +--------------+-------------+ mysql> SELECT CEILING(-14.7), CEILING(14.7); +----------------+---------------+ | CEILING(-14.7) | CEILING(14.7) | +----------------+---------------+ |            -14 |            15 | +----------------+---------------+

ABS() and SIGN() extract the absolute value and sign of numeric values:

 mysql> SELECT ABS(-14.7), ABS(14.7); +------------+-----------+ | ABS(-14.7) | ABS(14.7) | +------------+-----------+ | 14.7       | 14.7      | +------------+-----------+ mysql> SELECT SIGN(-14.7), SIGN(14.7), SIGN(0); +-------------+------------+---------+ | SIGN(-14.7) | SIGN(14.7) | SIGN(0) | +-------------+------------+---------+ |          -1 |          1 |       0 | +-------------+------------+---------+

A family of functions performs trigonometric calculations, including conversions between degrees and radians:

 mysql> SELECT SIN(0), COS(0), TAN(0); +--------+--------+--------+ | SIN(0) | COS(0) | TAN(0) | +--------+--------+--------+ |      0 |      1 |      0 | +--------+--------+--------+ mysql> SELECT PI(), DEGREES(PI()), RADIANS(180); +----------+---------------+-----------------+ | PI()     | DEGREES(PI()) | RADIANS(180)    | +----------+---------------+-----------------+ | 3.141593 |           180 | 3.1415926535898 | +----------+---------------+-----------------+

To generate random numbers, invoke the RAND() function:

 mysql> SELECT RAND(), RAND(), RAND(); +------------------+------------------+------------------+ | RAND()           | RAND()           | RAND()           | +------------------+------------------+------------------+ | 0.55239934711941 | 0.16831658330589 | 0.18438490590489 | +------------------+------------------+------------------+

10.6.5. String Functions

String functions calculate string lengths, extract pieces of strings, search for substrings or replace them, perform lettercase conversion, and more.

The LENGTH() and CHAR_LENGTH() functions determine string lengths in byte and character units, respectively. The values returned by the two functions will differ for strings that contain multi-byte characters. The following example shows this, using the latin1 single-byte character set and the ucs2 double-byte character set:

 mysql> SET @s = CONVERT('MySQL' USING latin1); mysql> SELECT LENGTH(@s), CHAR_LENGTH(@s); +------------+-----------------+ | LENGTH(@s) | CHAR_LENGTH(@s) | +------------+-----------------+ |          5 |               5 | +------------+-----------------+ mysql> SET @s = CONVERT('MySQL' USING ucs2); mysql> SELECT LENGTH(@s), CHAR_LENGTH(@s); +------------+-----------------+ | LENGTH(@s) | CHAR_LENGTH(@s) | +------------+-----------------+ |         10 |               5 | +------------+-----------------+

CONCAT() and CONCAT_WS() concatenate strings. CONCAT() concatenates all of its arguments, whereas CONCAT_WS() interprets its first argument as a separator to place between the following arguments:

 mysql> SELECT CONCAT('aa','bb','cc','dd'); +-----------------------------+ | CONCAT('aa','bb','cc','dd') | +-----------------------------+ | aabbccdd                    | +-----------------------------+ mysql> SELECT CONCAT_WS('aa','bb','cc','dd'); +--------------------------------+ | CONCAT_WS('aa','bb','cc','dd') | +--------------------------------+ | bbaaccaadd                     | +--------------------------------+

The two functions also differ in their handling of NULL values. CONCAT() returns NULL if any of its arguments are null. CONCAT_WS() ignores NULL values:

 mysql> SELECT CONCAT('/','a',NULL,'b'), CONCAT_WS('/','a',NULL,'b'); +--------------------------+-----------------------------+ | CONCAT('/','a',NULL,'b') | CONCAT_WS('/','a',NULL,'b') | +--------------------------+-----------------------------+ | NULL                     | a/b                         | +--------------------------+-----------------------------+

The STRCMP() function compares two strings and returns 1, 0, or 1 if the first string is less than, equal to, or greater than the second string, respectively:

 mysql> SELECT STRCMP('abc','def'), STRCMP('def','def'), STRCMP('def','abc'); +---------------------+---------------------+---------------------+ | STRCMP('abc','def') | STRCMP('def','def') | STRCMP('def','abc') | +---------------------+---------------------+---------------------+ |                  -1 |                   0 |                   1 | +---------------------+---------------------+---------------------+

MySQL encrypts passwords in the grant tables using the PASSWORD() function. This function should be considered for use only for managing MySQL accounts, not for general user applications. One reason for this is that applications often require reversible (two-way) encryption, and PASSWORD() performs irreversible (one-way) encryption. Another reason that applications should avoid reliance on PASSWORD() is that its implementation may change. (In fact, it did change in MySQL 4.1.0 and again in 4.1.1.)

For applications that work with data that must not be stored in unencrypted form, MySQL provides several pairs of functions that perform two-way encryption and decryption:

ENCODE() and DECODE()
DES_ENCRYPT() and DES_DECRYPT()
AES_ENCRYPT() and AES_DECRYPT()

Cryptographically, AES_ENCRYPT() and AES_DECRYPT() can be considered the most secure of the pairs. DES_ENCRYPT() and DES_DECRYPT() can be used if SSL support is enabled. Other details can be found in the MySQL Reference Manual.

10.6.6. Temporal Functions

Temporal functions perform operations such as extracting parts of dates and times, reformatting values, or converting values to seconds or days. In many cases, a temporal function that takes a date or time argument also can be given a datetype argument and will ignore the irrelevant part of the datetime value.

There are functions for extracting parts of date or time values:

 mysql> SET @d = '2010-04-15', @t = '09:23:57'; mysql> SELECT YEAR(@d), MONTH(@d), DAYOFMONTH(@d); +----------+-----------+----------------+ | YEAR(@d) | MONTH(@d) | DAYOFMONTH(@d) | +----------+-----------+----------------+ |     2010 |         4 |             15 | +----------+-----------+----------------+ mysql> SELECT DAYOFYEAR(@d); +---------------+ | DAYOFYEAR(@d) | +---------------+ |           105 | +---------------+ mysql> SELECT HOUR(@t), MINUTE(@t), SECOND(@t); +----------+------------+------------+ | HOUR(@t) | MINUTE(@t) | SECOND(@t) | +----------+------------+------------+ |        9 |         23 |         57 | +----------+------------+------------+

MAKEDATE() and MAKETIME() compose dates and times from component values. MAKEDATE() produces a date from year and day of year arguments:

 mysql> SELECT MAKEDATE(2010,105); +--------------------+ | MAKEDATE(2010,105) | +--------------------+ | 2010-04-15         | +--------------------+

MAKETIME() produces a time from hour, minute, and second arguments.

 mysql> SELECT MAKETIME(9,23,57); +-------------------+ | MAKETIME(9,23,57) | +-------------------+ | 09:23:57          | +-------------------+

If you need to determine the current date or time, use CURRENT_DATE or CURRENT_TIME. To get the current date and time as a single value, use CURRENT_TIMESTAMP or NOW():

 mysql> SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP; +--------------+--------------+---------------------+ | CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP   | +--------------+--------------+---------------------+ | 2005-05-31   | 21:40:18     | 2005-05-31 21:40:18 | +--------------+--------------+---------------------+

The three functions in the preceding statement are unlike most functions in that they can be invoked with or without parentheses following the function name.

10.6.7. `NULL`-Related Functions

Functions intended specifically for use with NULL values include ISNULL() and IFNULL(). ISNULL() is true if its argument is NULL and false otherwise:

 mysql> SELECT ISNULL(NULL), ISNULL(0), ISNULL(1); +--------------+-----------+-----------+ | ISNULL(NULL) | ISNULL(0) | ISNULL(1) | +--------------+-----------+-----------+ |            1 |         0 |         0 | +--------------+-----------+-----------+

IFNULL() takes two arguments. If the first argument is not NULL, that argument is returned; otherwise, the function returns its second argument:

 mysql> SELECT IFNULL(NULL,'a'), IFNULL(0,'b'); +------------------+---------------+ | IFNULL(NULL,'a') | IFNULL(0,'b') | +------------------+---------------+ | a                | 0             | +------------------+---------------+

Other functions handle NULL values in various ways, so you have to know how a given function behaves. In many cases, passing a NULL value to a function results in a NULL return value. For example, any NULL argument passed to CONCAT() causes it to return NULL:

 mysql> SELECT CONCAT('a','b'), CONCAT('a',NULL,'b'); +-----------------+----------------------+ | CONCAT('a','b') | CONCAT('a',NULL,'b') | +-----------------+----------------------+ | ab              | NULL                 | +-----------------+----------------------+

But not all functions behave that way. CONCAT_WS() (concatenate with separator) simply ignores NULL arguments entirely:

 mysql> SELECT CONCAT_WS('/','a','b'), CONCAT_WS('/','a',NULL,'b'); +------------------------+-----------------------------+ | CONCAT_WS('/','a','b') | CONCAT_WS('/','a',NULL,'b') | +------------------------+-----------------------------+ | a/b                    | a/b                         | +------------------------+-----------------------------+

For information about the behavior of specific functions with respect to NULL, consult the MySQL Reference Manual.