<<

. 119
( 132 .)



>>

the expression do not match:

mysql> select ˜jay greenspan™ NOT LIKE ˜jay%™;
+---------------------------------+
| ˜jay greenspan™ NOT LIKE ˜jay%™ |
+---------------------------------+
| 1|
+---------------------------------+


REGEXP
This function performs a pattern match of a string expression (expr) against a reg-
ular expression (pat). See Appendix G for a discussion of regular expressions. But
be aware that MySQL does not support regular expressions to the extent you find
in PHP.

expr REGEXP pat
RETURNS: int

REGEXP returns 1 (true) if the pattern is found or 0 (false) if not:

mysql> select name from guestbook WHERE name regexp ˜^j.*g™;
+---------------+
| name |
+---------------+
| Jay Greenspan |
| Jay Green |
+---------------+
2 rows in set (0.00 sec)


NOT REGEXP
This function works identically to REGEXP, except that patterns that fail to match
the expression test true and those that do match test false.
Appendix J: MySQL Function Reference 699


STRCMP
This function compares two strings, like the PHP function of the same name:

STRCMP(expr1,expr2) (used in examples)
RETURNS: int

STRCMP returns 0 if the strings are the same, -1 if the first argument is smaller
than the second, and 1 if the second argument is smaller than the first:

mysql> select strcmp(˜foo™, ˜bar™);
+----------------------+
| strcmp(˜foo™, ˜bar™) |
+----------------------+
| 1|
+----------------------+
1 row in set (0.11 sec)

mysql> select strcmp(˜bar™, ˜bar™);
+----------------------+
| strcmp(˜bar™, ˜bar™) |
+----------------------+
| 0|
+----------------------+
1 row in set (0.00 sec)

mysql> select strcmp(˜bar™, ˜foo™);
+----------------------+
| strcmp(˜bar™, ˜foo™) |
+----------------------+
| -1 |
+----------------------+
1 row in set (0.00 sec)


MATCH...AGAINST
Starting in MySQL version 3.23, MySQL incorporates full-text searching. Using
full-text searching you test the relevance of given rows against a string pattern. We
didn™t use full-text searching in the applications in this book, but if you™re inter-
ested in this feature we recommend reading Section 6.8 of the MySQL manual,
available at http://www.mysql.com/doc/F/u/Fulltext_Search.html.
700 Part V: Appendixes


Cast Operators
You will encounter only one cast operator in MySQL.

BINARY
BINARY
RETURNS: string

The BINARY operator casts the string following it to a binary string. Using it is an
easy way to force a column comparison to be case-sensitive even if the column
isn™t defined as BINARY or BLOB.

mysql> select binary(˜Foo™) = ˜foo™, binary(˜Foo™) = ˜Foo™;
+-----------------------+-----------------------+
| binary(˜Foo™) = ˜foo™ | binary(˜Foo™) = ˜Foo™ |
+-----------------------+-----------------------+
| 0| 1|
+-----------------------+-----------------------+
1 row in set (0.06 sec)




Control Flow Functions
Two functions allow for varying results depending on conditions.

IFNULL
IFNULL(expr1,expr2) (used in examples)
RETURNS: type of expr1 or expr2

If expr1 is not NULL, IFNULL() returns expr1; otherwise, it returns expr2.
IFNULL() returns either a numeric or a string value depending on the context in
which it is used.

mysql> select ifnull(1/0, ˜exp 1 is null™);
+------------------------------+
| ifnull(1/0, ˜exp 1 is null™) |
+------------------------------+
| exp 1 is null |
+------------------------------+
1 row in set (0.00 sec)

mysql> select ifnull(1/1, ˜exp 1 is not null™);
+----------------------------------+
Appendix J: MySQL Function Reference 701

| ifnull(1/1, ˜exp 1 is not null™) |
+----------------------------------+
| 1.00 |
+----------------------------------+
1 row in set (0.00 sec)


IF
Lots of times, you need to do something only if one or more conditions are true. IF
serves that purpose in MySQL queries.

IF(expr1,expr2,expr3) (used in examples)

If expr1 is true (expr1 <> 0 and expr1 <> NULL), IF() returns expr2; other-
wise it returns expr3. IF() returns a numeric or string value depending on the con-
text in which it is used. expr1 is evaluated as an integer value, which means that if
you are testing floating-point or string values you should do so using a comparison
operation.

mysql> select if(name like ˜jay%™, ˜Yes™, ˜No™) as ˜Jay Names™
-> from guestbook;
+-----------+
| Jay Names |
+-----------+
| Yes |
| Yes |
| No |
| Yes |
| No |
| No |
| No |
+-----------+
10 rows in set (0.00 sec)


NULLIF
NULLIF(expr1,expr2)

The NULLIF function compares the two expressions. If they are equal the func-
tion returns a NULL value. If they are not equal it returns the value of expr1.

mysql> select NULLIF(˜jay™, ˜jay™);
+----------------------+
| NULLIF(˜jay™, ˜jay™) |
+----------------------+
| NULL |
702 Part V: Appendixes

+----------------------+
1 row in set (0.00 sec)

mysql> select NULLIF(˜jay™, ˜jack™);
+-----------------------+
| NULLIF(˜jay™, ˜jack™) |
+-----------------------+
| jay |
+-----------------------+
1 row in set (0.00 sec)




Mathematical Functions
You can see the most current list of MySQL™s math functions at http://www.
mysql.com/doc/M/a/Mathematical_functions.html. All mathematical functions
return NULL in case of an error.

ABS
This function returns the absolute value of X:

ABS(X)
RETURNS: type of X

mysql> select abs(22), abs(-22);
+---------+----------+
| abs(22) | abs(-22) |
+---------+----------+
| 22 | 22 |
+---------+----------+


SIGN
This function returns the sign of the argument as -1, 0, or 1, depending on whether
X is negative, 0, or positive:

SIGN(X)RETURNS: intmysql> select sign(10), sign(-10), sign(0);
+----------+-----------+---------+
| sign(10) | sign(-10) | sign(0) |
+----------+-----------+---------+
| 1| -1 | 0|
+----------+-----------+---------+
1 row in set (0.00 sec)
Appendix J: MySQL Function Reference 703


MOD
Modulo is like the % operator in C. It returns the remainder of N divided by M:

MOD(N,M) or N % M
RETURNS: int

mysql> select mod(10,3), mod(10,4);
+-----------+-----------+
| mod(10,3) | mod(10,4) |
+-----------+-----------+
| 1| 2|
+-----------+-----------+
1 row in set (0.05 sec)


FLOOR
This function returns the largest integer value not greater than X:

FLOOR(X)
RETURNS: int

mysql> select floor(8.5);
+------------+
| floor(8.5) |
+------------+
| 8|
+------------+
1 row in set (0.00 sec)


CEILING
This function returns the smallest integer value not less than X:

CEILING(X)
RETURNS: int

mysql> select ceiling(8.5);
+--------------+
| ceiling(8.5) |
+--------------+
| 9|
+--------------+
1 row in set (0.00 sec)
704 Part V: Appendixes


ROUND
This function returns the argument X, rounded to an integer, rounded to the speci-
fied number of decimal places (or zero places by default):

<<

. 119
( 132 .)



>>