15.4 Functions

As you've seen in many examples so far, functions and operators can be used in SQL statements. In this section, we show you selected functions and operators and provide more examples. We've chosen to show you only those functions and operators that we regularly use in web database applications, but there are many others that you may find useful for mathematical operations, date and time comparisons, and string processing. A full list with examples is available in Section 6.3 of the MySQL manual.

15.4.1 Arithmetic and Comparison Operators

Table 15-1 shows examples of the basic arithmetic and comparison operators and their output when tested with a SELECT statement. The basic arithmetic operators are *, +, /, and -, as well as the parentheses ( ) that are used to control the order of evaluation of an expression.

SELECT 8+3*2;

14

SELECT (8+3)*2;

22

SELECT 2=2;

1

SELECT 1!=2;

1

SELECT 2<=2;

1

SELECT 3<=2;

0

SELECT 'Apple' = 'Apple';

1

SELECT 'Apple' < 'Banana';

1

SELECT 'Banana' BETWEEN 'Apple' AND 'Carrot';

1

SELECT 7 NOT BETWEEN 2 AND 5;

1

SELECT 6 IN (6, 'cat', 3.14);

1

SELECT 6 NOT IN (6, 'cat', 3.14);

0

SELECT NULL IS NULL;

1

SELECT 0 IS NULL;

0

SELECT 0 IS NOT NULL;

1

SELECT NULL = 0;

NULL

SELECT NULL <=> NULL;

1

SELECT NULL <=> 0;

0

The comparison operators include =, !=, <, >, <=, >=, and <=>. If an expression evaluates as true, the output is 1; if an expression evaluates as false, the output is 0. When you compare values of the same type, they are compared using that type of comparison; for example, when you compare integers to integers, they are compared numerically as integer values, and when you compare two strings they're compared alphabetically as strings. For string comparison, case is ignored and so is any trailing whitespace.

To test for equality, a single equals sign = is used; this is different from PHP, where the double equals == is used for equality tests, and a single equals sign = is used for assignment. However, if you compare NULL to any other value (including NULL) with the single equals sign = then the result is NULL. MySQL therefore includes a NULL -safe equality comparison operator <=> that returns 1 when two NULL values are compared and 0 when a NULL value is compared to any other value. You can also explicitly test whether a value is NULL by using IS NULL, and not NULL using IS NOT NULL. The other basic operators work the same as in PHP as discussed in Chapter 2.

The BETWEEN operator returns 1 if a value lies in the range defined by the following two parameters, inclusive. The NOT BETWEEN operator does the opposite. The IN operator returns 1 if a the value preceding the operator is in the set that's listed after the operator, and NOT IN does the opposite.

15.4.1.1 String functions

Table 15-2 shows examples using the MySQL string functions. There are also functions for converting integers to strings, strings to integers, and integers to different numbering schemes such as octal, hexadecimal, and binary; we've omitted these, but you'll find more details in the MySQL manual. Regular expressions can also be used through the function regexp( ); for more on regular expressions, see Chapter 3.

SELECT 'Apple' LIKE 'A%';

1

SELECT 'Apple' LIKE 'App%';

1

SELECT 'Apple' LIKE 'A%l%';

1

SELECT 'Apple' LIKE 'Appl_';

1

SELECT 'Apple' LIKE 'Appl_  _';

0

SELECT concat('con','cat');

concat

SELECT concat('con','c','at');

concat

SELECT concat_ws(",", "Williams", "Lucy");

Williams,Lucy

SELECT length('Apple');

5

SELECT locate('pp','Apple');

2

SELECT locate('pp','Apple',3);

0

SELECT lower('Apple');

apple

SELECT ltrim('  Apple');

Apple

SELECT rtrim('Apple  ');

Apple

SELECT quote("Won't");

'Won\'t'

SELECT replace('The Web', 'Web', 'WWW');

The WWW

SELECT strcmp('a','a');

0

SELECT strcmp('a','b');

-1

SELECT strcmp('b','a');

1

SELECT strcmp('A','a');

0

SELECT substring('Apple',2,3);

ppl

SELECT trim('  Apple  ');

Apple

SELECT upper('Apple');

APPLE

The string functions work as follows:

LIKE

A useful way to compare a string with an approximate representation of a string. For example, you can use it to find all rows that begin with a character or prefix. The % character is a wildcard that represents any number of unspecified characters. So, for example, the comparison of the string 'Apple' LIKE 'A%' returns 1, as does the comparison of `Apple' LIKE 'App%'. The underscore character can be used to match a single wildcard character. For example, `Apple' LIKE `Appl_' returns 1, while 'Appl' LIKE 'Appl_' returns 0.

concat( )

Joins (concatenates) two or more strings together and returns a single string consisting of the parameters.

concat_ws( )

Joins two or more strings together using the first parameter as a separator and returns a single string. It ignores any parameters that are NULL.

length( )

Returns the length of the string in characters.

locate( )

Returns the location of the first string parameter in the second string parameter. If the string doesn't occur, the result is 0. If the optional third parameter is provided, the search begins at that offset.

replace( )

Replaces all occurrences of the second parameter in the first parameter with the third parameter, and returns the modified string.

substring( )

Returns part of the string passed as the first parameter. The string that is returned begins at the offset supplied as the second parameter and is of the length supplied as the third parameter.

ltrim( )

Removes any left-padding space characters from the string parameter and returns the left-trimmed string.

rtrim( )

Removes any right-padding space characters from the string parameter and returns the right-trimmed string.

trim( )

Performs the function of both ltrim( ) and rtrim( ). Any leading or trailing spaces are removed, and the trimmed string is returned.

quote( )

Puts quotation marks around a string, and escapes any characters that need to be escaped. This is useful for preparing a string to be used in an SQL statement.

strcmp( )

Compares two string parameters, and returns a case-sensitive value that indicates the alphabetic ordering of the strings. If they are identical, it returns 0. If the first string is alphabetically less than the second, it returns a negative number. If the first string is alphabetically greater than the second, it returns a positive number. Uppercase characters are less than lowercase characters.

lower( )

Converts the string parameter to lowercase and returns the lowercase string.

upper( )

Converts the string parameter to uppercase and returns the uppercase string.

15.4.1.2 Mathematical functions

We make little use of the mathematical functions provided by MySQL in this book, and that's true of most web database applications. However, Table 15-3 shows selected key MySQL mathematical functions you can use and their output.

SELECT abs(-33);

33

SELECT abs(33);

33

SELECT ceiling(3.14159);

4

SELECT cos(pi( ));

-1.000000

SELECT floor(3.14159);

3

SELECT format(12345.23,0);

12,345

SELECT format(12345.23, 1);

12,345.2

SELECT ln(10);

2.302585

SELECT log(100,3);

0.238561

SELECT log10(100);

2

SELECT mod(10,3);

1

SELECT 10 % 3;

1

SELECT pow(4,2);

16.000000

SELECT rand( );

0.88605689619301

SELECT round(3.14159);

3

SELECT sin(pi( ));

0.000000

SELECT sqrt(36);

6.000000

SELECT tan(pi( ));

-0.000000

SELECT truncate(3.14159,3);

3.141

Several of the functions in Table 15-3 require some explanation:

abs( )

Returns the absolute value of a number: it removes the negative sign from negative numbers.

% and mod( )

Modulo has two syntaxes with identical effects. These divide the first number by the second number and output the remainder.

floor( ) and ceiling( )

These are complementary: floor( ) returns the largest integer not greater than the parameter, while ceiling( ) returns the smallest integer not less than the parameter.

round( )

Rounds to the nearest integer and returns the result.

ln( ) , log( ) , and log10( )

These are natural, parameterizable, and base-10 logarithm functions respectively. The second parameter to the log( ) function is the base to use, and if the parameter is omitted it behaves the same as ln( ). All return the result of the operation.

pow( )

Raises the first number to the power of the second and returns the result.

sqrt( )

Takes the square root of the parameter and returns the result.

sin( ) , cos( ) , and tan( )

These trigonometry functions take values expressed in radians as parameters, and return the sine, cosine, and tangent of the parameter as a result. The complementary arc sine, arc cosine, and arc tangent are available as asin( ) , acos( ) , and atan( ) .

pi( )

Returns the value of Pi.

rand( )

Returns a pseudo-random number in the range 0 to 1.

truncate( )

Removes decimal places without rounding and returns the result.

format( )

This isn't really a mathematical function but is instead used for returning numbers in a predefined format. The first parameter is the number, and the second parameter is the number of decimal places to return. The first parameter is rounded so that, for example, 123.56 formatted to one decimal place is 123.6.

15.4.1.3 Date and time functions

Table 15-4 shows sample uses of selected time and date functions available in MySQL. However, you'll find that most of your date and time manipulation in a web database application occurs in your PHP scripts, and for that reason, we've kept this section brief; we discuss PHP date and time manipulation in Chapters Chapter 3 and Chapter 9. MySQL functions for date and time manipulation are described in detail in Section 6.3.4 of the MySQL manual.

SELECT curdate( );

2002-01-01

SELECT curtime( );

11:27:20

SELECT date('2005-10-10 12:22:54');

2005-10-10

SELECT date_add('2005-05-03', INTERVAL 1 DAY);

2005-05-04

SELECT date_format(now( ), "%W, %e %M, %Y.");

Tuesday, 30 September, 2003.

SELECT dayofweek('2000-05-03');

3

SELECT dayname('2000-05-03');

Wednesday

SELECT dayofyear('2000-05-03');

124

SELECT monthname('2000-05-03');

May

SELECT extract(YEAR FROM '2005-01-01 11:27:20');

2005

SELECT now( );

2005-01-01 11:27:20

SELECT quarter('2000-05-03');

2

SELECT time('2005-10-10 12:22:54');

12:22:54

SELECT timestamp('2005-10-10');

2005-10-10 12:35:10

SELECT week('2000-05-03');

18

SELECT weekday('2000-05-03');

2

Here are some the key issues related to MySQL data and time functions:

date_add( )

We've provided only one example of using this function. This function can add over 20 different types of values (including seconds, minutes, hours, days, months, years, and combinations of these) to a variety of different date and time formats. It's described in detail in the MySQL manual, with many examples. There's also a complementary date_sub( ) function for subtraction.

curdate( ), curtime( ) , and now( )

The curdate( ) and curtime( ) functions return the current date and time respectively, and now( ) returns both. These functions are evaluated before a query begins, and so multiple calls to them in the same query will return the same result.

extract( )

We've provided only one example of using this function, showing how it retrieves a component from a date or combined date and time. It supports the same wide range of parameters as date_add( ).

date_format( )

This can take over 20 different parameters to control the output of a date in almost any desired format. We've only shown one example, but the parameters and many more examples are in the MySQL manual.

week( )

Returns the number of the week in the year in the range 0 to 53. You can provide a second parameter that controls whether a week begins on Sunday or Monday, and whether the function returns values from 0 to 53 or 1 to 53. With 0 (the default) you get a Sunday start, and 0 to 53 as a result. With 1, it's Monday and 0 to 53; with 2, Sunday and 1 to 53; and, with 3, Monday and 1 to 53.

timestamp( )

Converts a date into a timestamp that includes a date and time. You can extract a Unix timestamp (the number of seconds since 1 January 1970) using unix_timestamp( ).

15.4.1.4 Miscellaneous operators and functions

Miscellaneous operators and functions are shown in Table 15-5.

SELECT if(1<0,"yes","no")

no

SELECT decode('"|2 1~','shhh')

secret

SELECT encode('secret','shhh')

"|2 1~

SELECT md5('secret');

5ebe2294ecd0e0f08eab7690d2a6ee69

SELECT password('secret')

*aace71a608b0b77c141250293c9f9b5b7ec75c970ea7

SELECT database( )

winestore

SELECT user( )

Here's a short discussion of these functions:

if

This conditional function outputs the first string if the expression is true and the second if it is false. This can be used in complex ways. For example, it could be used in an UPDATE statement for intelligent changes to an attribute:

UPDATE orders SET instructions =

  if(trim(instructions)='','None specified',instructions);

In this case, the SQL statement replaces blank instructions attributes with a string and leaves already filled instructions unaltered.

decode( ) and encode( )

These functions are related two way functions that can be used to encrypt and decrypt data using a password. Encryption is discussed in Chapter 11.

password( )

This is a one way encryption function that converts a plain-text string into an encoded string; it's also used internally by MySQL to store passwords in the users table that's discussed in Section 15.8. Encryption is discussed in Chapter 11.

md5( )

This function produces an MD5 hash or digest of the string parameter. Encryption is discussed in Chapter 11.

database( ) and user( )

These functions provide the names of the current database and user, respectively.