Learning PHP, MySQL, JavaScript, CSS & HTML5, 3rd Edition

Appendix D. MySQL Functions

Having functions built into MySQL substantially reduces the speed of performing complex queries, as well as their complexity. If you wish to learn more about the available functions, you can visit the following URLs:

• String functions

• Date and time

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

String Functions

CONCAT(str1, str2, ...)

Returns the result of concatenating str1, str2, and any other parameters (or NULL if any argument is NULL). If any of the arguments are binary, then the result is a binary string; otherwise, the result is a non-binary string. The code returns the string "MySQL":

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

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

This 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. This 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). This code returns the string "Columbus":

SELECT RIGHT('Christopher Columbus', '8');

MID(str, pos, len)

Returns up to len characters from the string str starting ...