MySQL Functions - Learning PHP, MySQL, & JavaScript

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');

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');

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');

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');

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', '6', '4');

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 10:

SELECT LENGTH('Tony Blair');

Returns the string str padded to a length of len characters by prepending the string with padstr characters. If str is longer than len then the string returned will be truncated to len characters. The example code returns the following 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', ' ');

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', '!');

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 zero 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);

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');

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');

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");

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';

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 Coke”:

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

Returns the string str with all remove 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__');

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   ');

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, 2010, the following code returns the values 2010-05-02 and 20100502:

SELECT CURDATE();
SELECT CURDATE() + 0;

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

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

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, “Expected expr values” 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. Although 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 '2010-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
2011-01-01 00:00:00
1999-12-31 23:59:59

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

This returns the date value formatted according to the format string. Table D.2, “DATE_FORMAT specifiers” 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 “Thursday May 4th 2006 03:02 AM”:

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

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 “2010-00-00”. You can also use the function DAYOFMONTH to return the same value. This code returns the value 3:

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

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

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

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

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

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

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

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('2011-02-03');
SELECT LAST_DAY('2011-03-11');
SELECT LAST_DAY('2011-04-26');

returns the following values:

2011-02-28
2011-03-31
2011-04-30

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

Returns a date given year and dayofyear values. If dayofyear is zero, the result is NULL. This code returns the date “2011-10-01”:

SELECT MAKEDATE(2011,274);

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 “2012-00-00”, return zero. This code returns the value 7:

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

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

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

Returns the current date and time as a value in either YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS 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, 2011, this code returns the values 2011-12-19 19:11:13 and 20111219191113:

SELECT SYSDATE();
SELECT SYSDATE() + 0;

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');

Returns the week number for date. If passed the optional mode parameter, the week number returned will be modified according to Table D.3, “The modes supported by the WEEK function”. 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('2006-04-04', 1);

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

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

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 and 11:56:23.000000:

SELECT CURTIME() + 0;

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

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

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

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

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

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

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);

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');

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 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();

Returns the unix_timestamp parameter as either a string in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS.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 8-17. 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');