ONLamp.com    
 Published on ONLamp.com (http://www.onlamp.com/)
 See this if you're having trouble printing code examples


MySQL Functions - Learning PHP, MySQL, & JavaScript

by Robin Nixon

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

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

JavaScript: The Good Parts book cover

This excerpt is from Learning PHP, MySQL, and JavaScript . Discover how the powerful combination of PHP and MySQL provides an easy way to build modern websites complete with dynamic data and user interaction. You'll also learn how to add JavaScript to create rich Internet applications and websites.

buy button

String Functions

Name

CONCAT()

Synopsis

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

Name

CONCAT_WS()

Synopsis

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

Name

LEFT()

Synopsis

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

Name

RIGHT()

Synopsis

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

Name

MID()

Synopsis

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

Name

LENGTH()

Synopsis

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

SELECT LENGTH('Tony Blair');

Name

LPAD()

Synopsis

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

Name

RPAD

Synopsis

RPAD(str, len, padstr)

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

Name

LOCATE()

Synopsis

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

Name

LOWER()

Synopsis

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

Name

UPPER()

Synopsis

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

Name

QUOTE()

Synopsis

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

Name

REPEAT()

Synopsis

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

Name

REPLACE()

Synopsis

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

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

Name

TRIM()

Synopsis

TRIM([specifier remove FROM] str)

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

Name

LTRIM() and RTRIM()

Synopsis

LTRIM(str)
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.

Name

CURDATE()

Synopsis

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

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

Name

DATE()

Synopsis

DATE(expr)

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

Name

DATE_ADD()

Synopsis

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, “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.

Table D.1. Expected expr values

Type

Expected expr value

Example

MICROSECOND

MICROSECONDS

111111

SECOND

SECONDS

11

MINUTE

MINUTES

11

HOUR

HOURS

11

DAY

DAYS

11

WEEK

WEEKS

11

MONTH

MONTHS

11

QUARTER

QUARTERS

1

YEAR

YEARS

11

SECOND_MICROSECOND

'SECONDS.MICROSECONDS'

11.22

MINUTE_MICROSECOND

'MINUTES.MICROSECONDS'

11.22

MINUTE_SECOND

'MINUTES:SECONDS'

'11:22'

HOUR_MICROSECOND

'HOURS.MICROSECONDS'

11.22

HOUR_SECOND

'HOURS:MINUTES:SECONDS'

'11:22:33'

HOUR_MINUTE

'HOURS:MINUTES'

'11:22'

DAY_MICROSECOND

'DAYS.MICROSECONDS'

11.22

DAY_SECOND

'DAYS HOURS:MINUTES:SECONDS'

'11 22:33:44'

DAY_MINUTE

'DAYS HOURS:MINUTES'

'11 22:33'

DAY_HOUR

'DAYS HOURS'

'11 22'

YEAR_MONTH

'YEARS-MONTHS'

'11-2'


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.


Name

DATE_FORMAT()

Synopsis

DATE_FORMAT(date, format)

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

Table D.2. DATE_FORMAT specifiers

Specifier

Description

%a

Abbreviated weekday name (Sun–Sat)

%b

Abbreviated month name (Jan–Dec)

%c

Month, numeric (0–12)

%D

Day of the month with English suffix (0th, 1st, 2nd, 3rd, ...)

%d

Day of the month, numeric (00–31)

%e

Day of the month, numeric (0–31)

%f

Microseconds (000000–999999)

%H

Hour (00–23)

%h

Hour (01–12)

%I

Hour (01–12)

%i

Minutes, numeric (00–59)

%j

Day of year (001–366)

%k

Hour (0–23)

%l

Hour (1–12)

%M

Month name (January–December)

%m

Month, numeric (00–12)

%p

AM or PM

%r

Time, 12-hour (hh:mm:ss followed by AM or PM)

%S

Seconds (00–59)

%s

Seconds (00–59)

%T

Time, 24-hour (hh:mm:ss)

%U

Week (00–53), where Sunday is the first day of the week

%u

Week (00–53), where Monday is the first day of the week

%V

Week (01–53), where Sunday is the first day of the week; used with %X

%v

Week (01–53), where Monday is the first day of the week; used with %x

%W

Weekday name (Sunday–Saturday)

%w

Day of the week (0=Sunday–6=Saturday)

%X

Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V

%x

Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v

%Y

Year, numeric, four digits

%y

Year, numeric, two digits

%%

A literal % character



Name

DAY()

Synopsis

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

Name

DAYNAME()

Synopsis

DAYNAME(date)

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

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

Name

DAYOFWEEK()

Synopsis

DAYOFWEEK(date)

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

Name

DAYOFYEAR()

Synopsis

DAYOFYEAR(date)

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

Name

LAST_DAY()

Synopsis

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('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.


Name

MAKEDATE()

Synopsis

MAKEDATE(year, dayofyear)

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

Name

MONTH()

Synopsis

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

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

Name

MONTHNAME()

Synopsis

MONTHNAME(date)

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

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

Name

SYSDATE()

Synopsis

SYSDATE()

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;

Name

YEAR()

Synopsis

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

Name

WEEK()

Synopsis

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

Table D.3. The modes supported by the WEEK function

Mode

First day of week

Range

Where week 1 is the first week ...

0

Sunday

0–53

with a Sunday in this year

1

Monday

0–53

with more than 3 days this year

2

Sunday

1–53

with a Sunday in this year

3

Monday

1–53

with more than 3 days this year

4

Sunday

0–53

with more than 3 days this year

5

Monday

0–53

with a Monday in this year

6

Sunday

1–53

with more than 3 days this year

7

Monday

1–53

with a Monday in this year



Name

WEEKDAY()

Synopsis

WEEKDAY(date)

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

SELECT WEEKDAY('2006-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.

Name

CURTIME()

Synopsis

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

SELECT CURTIME() + 0;

Name

HOUR()

Synopsis

HOUR(time)

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

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

Name

MINUTE()

Synopsis

MINUTE(time)

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

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

Name

SECOND()

Synopsis

SECOND(time)

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

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

Name

MAKETIME()

Synopsis

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

Name

TIMEDIFF()

Synopsis

TIMEDIFF(expr1, expr2)

Returns the difference between expr1 and expr2 (expr1expr2) 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');

Name

UNIX_TIMESTAMP()

Synopsis

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

Name

FROM_UNIXTIME()

Synopsis

FROM_UNIXTIME(unix_timestamp [, format])

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

If you enjoyed this excerpt, buy a copy of Learning PHP, MySQL, & JavaScript.

Copyright © 2009 O'Reilly Media, Inc.