3.9. Functions
Functions
and operators can be used in SQL statements. This section lists these
functions and operators and provides examples. A full list of
functions with examples is available in Section 7.4 of the
manual.html file distributed with MySQL.
3.9.1. Arithmetic and comparison operators
Table 32 shows examples of the basic
arithmetic and
comparison operators in
SELECT statements. The basic arithmetic operators
are *, +, /,
and , as well as the parentheses (
) to control the order of evaluation of an expression.
Table 32. Using the arithmetic and comparison operators
Statement

Output

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

The comparison operators include =,
!=, <,
>, <=, and
>=. Four examples are shown in Table 32. If an expression evaluates as
true, the output is 1; if an expression evaluates
as false, the output is 0. To test for equality, a
single equals sign is used; this contrasts with PHP, where the double
equals (==) is used for equality tests, and a
single equals sign is used for assignment.
To test whether two items are equal, the !=
operator is provided. Lessthanorequalto is represented by
<=, and greaterthanorequalto is represented
by >=. Parentheses can explicitly express the
evaluation order.
3.9.1.1. Stringcomparison operators and functions
Table 33 shows examples of the MySQL
stringcomparison operators
and functions. Many of the MySQL string functions shown here are
similar to PHP functions, which were introduced in Chapter 2.
Table 33. Using string comparison functions and operators
Statement

Output

SELECT 'Apple' LIKE 'A%';

1

SELECT 'Apple' LIKE 'App%';

1

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

1

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

'concat'

SELECT length('Apple');

5

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

2

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

'ppl'

SELECT ltrim(' Apple');

'Apple'

SELECT rtrim('Apple ');

'Apple'

SELECT trim(' Apple ');

'Apple'

SELECT space(3);

' '

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

0

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

1

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

1

SELECT lower('Apple');

'apple'

SELECT upper('Apple');

'APPLE'

The string functions work as follows:

The stringcomparison function LIKE is useful. The
% character represents any number of unspecified
characters, are generally known as wildcards. So, for example, the
comparison of the string 'Apple'
LIKE 'A%' is 1
(true), as is the comparison of
'Apple' LIKE
'App%'. The underscore character can be used to
match a single unspecified, wildcard character; for example,
'Apple' LIKE
'Appl_' is true, while
'Appl' LIKE
'Appl_' is false.

concat( ) joins or concatenates two strings together,
so the result of calling concat( ) with two string
parameters is a single string consisting of the parameters.

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.

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 leftpadding space characters
from the string parameter and returns the lefttrimmed string.

rtrim( )removes any
rightpadding space characters from the string parameter and returns
the righttrimmed string.

trim( ) performs the function of both ltrim(
) and rtrim( ); that is, any leading or
trailing spaces are removed, and the trimmed string is returned.

space( ) returns a string consisting of spaces of the
length of the integer parameter.

strcmp( ) compares two string parameters. 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.
While not detailed in Table 33, regular
expressions can be used through the function regexp(
). For more on regular expressions in PHP, see Chapter 2.
3.9.1.2. Mathematical functions
We make little use of the mathematical
functions provided by MySQL in this book. However, Table 34 shows selected MySQL mathematical functions
and their output.
Table 34. Using the MySQL mathematical functions
Statement

Output

SELECT abs(33);

33

SELECT abs(33);

33

SELECT mod(10,3);

1

SELECT 10 % 3;

1

SELECT floor(3.14159);

3

SELECT ceiling(3.14159);

4

SELECT round(3.14159);

3

SELECT log(100);

4.605170

SELECT log10(100);

2

SELECT pow(2,3);

8

SELECT sqrt(36);

6

SELECT sin(pi( ));

0.000000

SELECT cos(pi( ));

1.000000

SELECT tan(pi( ));

0.000000

SELECT rand( );

0.8536

SELECT truncate(3.14159,3);

3.141

SELECT format(12345.23,0);

12,345

SELECT format(12345.23, 1);

12,345.2

Several of the functions in Table 34 require some
explanation:

The abs( ) operator returns the absolute value of
a number; that is, it removes the negative sign from negative
numbers.

The modulo operator—which has two identical
variants, % and mod(
)—divides the first number by the second number and
outputs the remainder.

The floor( ) and ceiling( )
functions are complementary: floor( ) returns the
largest integer not greater than the parameter; ceiling(
) returns the smallest integer not less than the parameter.

The round( ) function rounds to the nearest
integer.

Both the natural logarithm, log( ), and base10
logarithm, log10( ), are available.

The pow( ) function raises the first number to the
power of the second.

sqrt( ) takes the square root of the parameter.

The trigonometry functions sin( ), cos(
), and tan( ) take values expressed in
radians as parameters. The complementary arc sin, arc cos, and arc
tan are available as asin( ), acos(
), and atan( ).

The rand( ) function returns a pseudorandom number
in the range 0 to 1.

The truncate( ) function removes decimal places
without rounding.

The format( ) function 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. This function is seldom used in web
database applications, because formatting is usually performed in PHP
scripts.
3.9.1.4. Miscellaneous operators and functions
Miscellaneous operators and functions are shown in Table 36.
Table 36. Miscellaneous operators and functions
Statement

Output

Control flow functions


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

no

Encryption functions


SELECT password('secret')

428567f408994404

SELECT encode('secret','shhh')

"ï ¨~

SELECT decode('"ï ¨~','shhh')

secret

Other functions


SELECT database( )

winestore

SELECT user( )

dimitria@localhost

The conditional function if 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 customer SET country =
if(trim(country)='','Australia',country);
In this case, the SQL statement replaces blank
country attributes with Australia and leaves
already filled country attributes unaltered.
Authentication and securing data using password(
), encode( ), and decode(
) are discussed in Chapter 9. The
functions database( ) and user(
) provide the names of the current database and
user.
   3.8. Modifying the Database   3.10. More on SQL and MySQL 
Copyright © 2003 O'Reilly & Associates. All rights reserved.
