Learning PHP, MySQL & JavaScript, 5th Edition

Appendix D. MySQL Functions

MySQL’s built-in functions substantially reduce the speed of complex queries, as well as their complexity. If you wish to learn more about the available functions, you can check out the documentation for the following:

• String functions

• Date and time functions

But, for easy reference, here are some of the most commonly used MySQL functions.

String Functions

The following is a selection of the most commonly encountered string manipulation functions:

CONCAT(str1, str2, ...)

Returns the result of combining str1, str2, and any other parameters (or NULL if any argument is NULL). If any of the arguments are binary, the result is a binary string; otherwise, the result is a nonbinary string. For instance, the following code returns the string "MySQL":

SELECT CONCAT('My', 'S', 'QL');

CONCAT_WS(separator, str1, str2, ...)

Works in the same way as CONCAT, except it inserts a separator between the items being concatenated. If the separator is NULL, the result will be NULL, but NULL values can be used as other arguments, which will then be skipped. The following code returns the string "Truman,Harry,S":

SELECT CONCAT_WS(',', 'Truman', 'Harry', 'S');

LEFT(str, len)

Returns the leftmost len characters from the string str (or NULL if any argument is NULL). The following code returns the string "Chris":

SELECT LEFT('Christopher Columbus', '5');

RIGHT(str, len)

Returns the rightmost len characters from the string str (or NULL if any argument is NULL ...