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

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

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). The following 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, 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. The following code returns the string "stop":

SELECT MID('Christopher Columbus', '5', '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. The following code returns the value 15:

SELECT LENGTH('Mark Zuckerberg');

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

Works the same as the LPAD function, except that the padding takes place on the right of the returned string. The following 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 0 is returned. The following 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);

Returns the string str with all the characters changed to lowercase (the inverse of UPPER). The following code returns the string "queen elizabeth ii":

SELECT LOWER('Queen Elizabeth II');

Returns the string str with all the characters changed to uppercase (the inverse of LOWER). The following 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 Ctrl-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, or, if count is less than 1, an empty string. If either parameter is NULL, the function returns NULL. The following 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. The following code returns the string "Cheeseburger and Soda":

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

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

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

Returns the string str with any leading spaces removed. The following code returns the string "No Padding  ":

SELECT LTRIM('   No Padding   ');

Returns the string str with any trailing spaces removed. The following code returns the string " 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, 2018, the following code returns the values 2018-05-02 and 20180502:

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

Extracts the date part of the date or a DATETIME expression expr. The following 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 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. The following 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.

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. The following 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');

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. The following code returns the value 3:

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

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

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

Returns the weekday index for date, from 1 for Sunday through 7 for Saturday. The following code returns the value 7:

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

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

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

Returns the last day of the month for the given DATETIME value date. If the argument is invalid, it returns NULL. The following 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

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

SELECT MAKEDATE(2018,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 2016-00-00, return 0. The following code returns the value 7:

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

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

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

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;

Returns the year for date, in the range 1000 to 9999, or 0 if date is 0000-00-00. The following 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. You can also use the function WEEKOFYEAR, which is equivalent to using the WEEK function with a mode of 3. The following code returns the week number 14:

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

Returns the weekday index for date, from 0 through 6 for Monday through Sunday. The following code returns the value 2:

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

Returns the current time (in the user’s time zone) in HH:MM:SS or HHMMSS format, depending on whether the function is used in a string or numeric context. If the optional fsp argument is supplied, with a value in the range of 0 to 6, the return value includes a fractional seconds part of that many digits. When the current time is 11:56:23, the following code returns the values 11:56:23 and 115623.000000:

SELECT CURTIME();
SELECT CURTIME() + 0;

Returns the hour for time. The following code returns the value 11:

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

Returns the minute for time. The following code returns the value 56:

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

Returns the second for time. The following code returns the value 23:

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

Returns a time value calculated from the hour, minute, and second arguments. The following 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. The following code returns the value 01:37:38:

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

Returns the number of seconds since 1970-01-01 00:00:00 UTC as an unsigned integer, if called without the optional date argument. If the date parameter is passed, the value returned is the number of seconds from 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). The following 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 YYY-MM-DD HH:MM:SS format or a floating-point number in YYYYMMDDHHMMSS 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. The following 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');