Learning PHP, MySQL & JavaScript: With jQuery, CSS & HTML5, Fourth Edition (2015)

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: tinyurl.com/phpstringfuncs

§ Date and time: tinyurl.com/phpdateandtime

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 nonbinary 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 at position pos. If len is omitted, then all characters up to the end of the string are returned. You may use a negative value for pos, in which case it represents the character pos places from the end of the string. The first position in the string is 1. This code returns the string "stop":

SELECT MID('Christopher Columbus', '5', '4');

LENGTH(str)

Returns the length in bytes of the string str. Note that multibyte characters count as multiple bytes. If you need to know the actual number of characters in a string, use the CHAR_LENGTH function. This code returns the value 15:

SELECT LENGTH('Mark Zuckerberg');

LPAD(str, len, padstr)

Returns the string str padded to a length of len characters by prepending the string with padstr characters. If str is longer than len, the string returned will be truncated to len characters. The example code returns these strings:

January

February

March

April

May

Notice how all the strings have been padded to be eight characters long:

SELECT LPAD('January', '8', ' ');

SELECT LPAD('February', '8', ' ');

SELECT LPAD('March', '8', ' ');

SELECT LPAD('April', '8', ' ');

SELECT LPAD('May', '8', ' ');

RPAD

This is the same as the LPAD function except that the padding takes place on the right of the returned string. This code returns the string "Hi!!!":

SELECT RPAD('Hi', '5', '!');

LOCATE(substr, str, pos)

Returns the position of the first occurrence of substr in the string str. If the parameter pos is passed, the search begins at position pos. If substr is not found in str, a value of 0 is returned. This code returns the values 5 and 11, because the first function call returns the first encounter of the word unit, while the second one only starts to search at the seventh character, and so returns the second instance:

SELECT LOCATE('unit', 'Community unit');

SELECT LOCATE('unit', 'Community unit' 7);

LOWER(str)

This is the inverse of UPPER. Returns the string str with all the characters changed to lowercase. This code returns the string "queen elizabeth ii":

SELECT LOWER('Queen Elizabeth II');

UPPER(str)

This is the inverse of LOWER. It returns the string str with all the characters changed to uppercase. This code returns the string "I CAN'T HELP SHOUTING":

SELECT UPPER("I can't help shouting");

QUOTE(str)

Returns a quoted string that can be used as a properly escaped value in a SQL statement. The returned string is enclosed in single quotes with all instances of single quotes, backslashes, the ASCII NUL character, and Control-Z preceded by a backslash. If the argument str is NULL, the return value is the word NULL without enclosing quotes. The example code returns the following string:

'I\'m hungry'

Note how the " symbol has been replaced with \'.

SELECT QUOTE("I'm hungry");

REPEAT(str, count)

Returns a string comprising count copies of the string str. If count is less than 1, an empty string is returned. If either parameter is NULL, then NULL is returned. This code returns the strings "Ho Ho Ho" and "Merry Christmas":

SELECT REPEAT('Ho ', 3), 'Merry Christmas';

REPLACE(str, from, to)

Returns the string str with all occurrences of the string from replaced with the string to. The search and replace is case-sensitive when searching for from. This code returns the string "Cheeseburger and Soda":

SELECT REPLACE('Cheeseburger and Fries', 'Fries', 'Soda');

TRIM([specifier remove FROM] str)

Returns the string str with all prefixes or suffixes removed. The specifier can be one of BOTH, LEADING, or TRAILING. If no specifier is supplied, then BOTH is assumed. The remove string is optional and, if omitted, spaces are removed. This code returns the strings "No Padding" and "Hello__":

SELECT TRIM(' No Padding ');

SELECT TRIM(LEADING '_' FROM '__Hello__');

LTRIM(str) and RTRIM(str)

The function RTRIM returns the string str with any leading spaces removed, while the function RTRIM performs the same action on the string’s tail. This code returns the strings "No Padding" and "No Padding":

SELECT LTRIM(' No Padding ');

SELECT RTRIM(' No Padding ');

Date Functions

Dates are an important part of most databases. Whenever financial transactions take place, the date has to be recorded, expiry dates of credit cards need to be noted for repeat billing purposes, and so on. So, as you might expect, MySQL comes with a wide variety of functions to make handling dates a breeze.

CURDATE()

Returns the current date in YYYY-MM-DD or YYYMMDD format, depending on whether the function is used in a numeric or string context. On the date May 2, 2018, the following code returns the values 2018-05-02 and 20180502:

SELECT CURDATE();

SELECT CURDATE() + 0;

DATE(expr)

Extracts the date part of the date or a DATETIME expression expr. This code returns the value 1961-05-02:

SELECT DATE('1961-05-02 14:56:23');

DATE_ADD(date, INTERVAL expr unit)

Returns the result of adding the expression expr using units unit to the date. The date argument is the starting date or DATETIME value, and expr may start with a - symbol for negative intervals. Table D-1 shows the interval types supported and the expected expr values. Note the examples in this table that show where it is necessary to surround the expr value with quotes for MySQL to correctly interpret them. If you are ever in doubt, adding the quotes will always work.

You can also use the DATE_SUB function to subtract date intervals. However, it’s not actually necessary for you to use the DATE_ADD or DATE_SUB functions, as you can use date arithmetic directly in MySQL. This code:

SELECT DATE_ADD('1975-01-01', INTERVAL 77 DAY);

SELECT DATE_SUB('1982-07-04', INTERVAL '3-11' YEAR_MONTH);

SELECT '2018-12-31 23:59:59' + INTERVAL 1 SECOND;

SELECT '2000-01-01' - INTERVAL 1 SECOND;

returns the following values:

1975-03-19

1978-08-04

2019-01-01 00:00:00

1999-12-31 23:59:59

Notice how the last two commands use date arithmetic directly without recourse to functions.

DATE_FORMAT(date, format)

This returns the date value formatted according to the format string. Table D-2 shows the specifiers that can be used in the format string. Note that the % character is required before each specifier, as shown. This code returns the given date and time as Friday May 4th 2018 03:02 AM:

SELECT DATE_FORMAT('2018-05-04 03:02:01', '%W %M %D %Y %h:%i %p');

DAY(date)

Returns the day of the month for date, in the range 1 to 31 or 0 for dates that have a zero day part such as 0000-00-00 or 2018-00-00. You can also use the function DAYOFMONTH to return the same value. This code returns the value 3:

SELECT DAY('2018-02-03');

DAYNAME(date)

Returns the name of the weekday for the date. This code returns the string "Saturday":

SELECT DAYNAME('2018-02-03');

DAYOFWEEK(date)

Returns the weekday index for date between 1 for Sunday through 7 for Saturday. This code returns the value 7:

SELECT DAYOFWEEK('2018-02-03');

DAYOFYEAR(date)

Returns the day of the year for date in the range 1 to 366. This code returns the value 34:

SELECT DAYOFYEAR('2018-02-03');

LAST_DAY(date)

Returns the last day of the month for the given DATETIME value date. If the argument is invalid, it returns NULL. This code:

SELECT LAST_DAY('2018-02-03');

SELECT LAST_DAY('2018-03-11');

SELECT LAST_DAY('2018-04-26');

returns the following values:

2018-02-28

2018-03-31

2018-04-30

As you’d expect, it correctly returns the 28th day of February, the 31st of March, and the 30th of April 2011.

MAKEDATE(year, dayofyear)

Returns a date given year and dayofyear values. If dayofyear is 0, the result is NULL. This code returns the date 2016-10-01:

SELECT MAKEDATE(2018,274);

MONTH(date)

Returns the month for date in the range 1 through 12 for January through December. Dates that have a zero month part, such as 0000-00-00 or 2016-00-00, return 0. This code returns the value 7:

SELECT MONTH('2018-07-11');

MONTHNAME(date)

Returns the full name of the month for date. This code returns the string "July":

SELECT MONTHNAME('2018-07-11');

SYSDATE()

Returns the current date and time as a value in either YYY-MM-DD HH:MM:SS or YYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. The function NOW works in a similar manner, except that it returns the time and date only at the start of the current statement, whereas SYSDATE returns the time and date at the exact moment the function itself is called. On December 19, 2018, at 19:11:13, this code returns the values 2018-12-19 19:11:13 and 20181219191113.

SELECT SYSDATE();

SELECT SYSDATE() + 0;

YEAR(date)

Returns the year for date in the range 1000 to 9999, or 0 for the zero date. This code returns the year 1999.

SELECT YEAR('1999-08-07');

WEEK(date [, mode])

Returns the week number for date. If passed the optional mode parameter, the week number returned will be modified according to Table D-3. You can also use the function WEEKOFYEAR, which is equivalent to using the WEEK function with a mode of 3. This code returns the week number 14.

SELECT WEEK('2018-04-04', 1);

WEEKDAY(date)

Returns the weekday index for date where 0=Monday through 6=Sunday. This code returns the value 2.

SELECT WEEKDAY('2018-04-04');

Time Functions

Sometimes you need to work with the time, rather than the date, and MySQL provides plenty of functions for you to do so.

CURTIME()

Returns the current time as a value in the format HH:MM:SS or HHMMSS.uuuuuu, depending on whether the function is used in a string or numeric context. The value is expressed using the current time zone. When the current time is 11:56:23, this code returns the values 11:56:23 and115623.000000.

SELECT CURTIME();

SELECT CURTIME() + 0;

HOUR(time)

Returns the hour for time. This code returns the value 11.

SELECT HOUR('11:56:23');

MINUTE(time)

Returns the minute for time. This code returns the value 56.

SELECT MINUTE('11:56:23');

SECOND(time)

Returns the second for time. This code returns the value 23.

SELECT SECOND('11:56:23');

MAKETIME(hour, minute, second)

Returns a time value calculated from the hour, minute, and second arguments. This code returns the time 11:56:23.

SELECT MAKETIME(11, 56, 23);

TIMEDIFF(expr1, expr2)

Returns the difference between expr1 and expr2 (expr1 – expr2) as a time value. Both expr1 and expr2 must be TIME or DATETIME expressions of the same type. This code returns the value 01:37:38.

SELECT TIMEDIFF('2000-01-01 01:02:03', '1999-12-31 23:24:25');

UNIX_TIMESTAMP([date])

If called without the optional date argument, this function returns the number of seconds since 1970-01-01 00:00:00 UTC as an unsigned integer. If the date parameter is passed, then the value returned is the number of seconds since the 1970 start date until the given date. This command will not return the same value for everyone because the date given to it is interpreted as a local time (given in the user’s time zone). This code will return the value 946684800 (the number of seconds up to the start of the new millennium) followed by a TIMESTAMP representing the current Unix time at the moment you run it.

SELECT UNIX_TIMESTAMP('2000-01-01');

SELECT UNIX_TIMESTAMP();

FROM_UNIXTIME(unix_timestamp [, format])

Returns the unix_timestamp parameter as either a string in YYY-MM-DD HH:MM:SS or YYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. If the optional format parameter is provided, the result is formatted according to the specifiers in Table D-2. The precise value returned will depend on the user’s local time. This code returns the strings "2000-01-01 00:00:00" and "Saturday January 1st 2000 12:00 AM".

SELECT FROM_UNIXTIME(946684800);

SELECT FROM_UNIXTIME(946684800, '%W %M %D %Y %h:%i %p');