SQL: Functions

November 26, 2020

List of the most frequently used SQL functions

Syntax: Functions are written like the following FUNCTION(parameters). Parameters are precised between brackets. These parameters are changing depending on the function used.
For more information on a particular function: click on the function's name to access to SQL detailed documentation page
Browse the complete list of SQL functions and operators from the official MySQL documentation here.

Summury

Numeric functions: list / video
Text functions: list / video
Time functions: list / video

1. Numeric functions

Additions / subtractions, etc. on a field: SELECT *, id + 2 -> Will return a new column called "id = 2" : id = 1 => id + 2 = 3, etc...

SUM(): Return a sum of all or part of values of a column
AVG(): Returns the average value of a column
TRUNCATE(): defines the number of digits after the decimal point for decimal numbers.
ABS(): converts to absolute value
MOD(): Function "modulo" (recovers the residue of a division between two noombres). In general, we will use the % sign (example: 6 % 4 -> Will return "2").
CEIL(): Rounded up to the next whole number.
FLOOR(): Rounded down to the nearest whole number.
SQRT(): Calculates the square root

2. Text functions

After WHERE statement (conditions):

LIKE(): Make advanced selections based on particular patterns
NOT_LIKE(): Search for values that don't match a particular pattern (opposite of LIKE)

After SELECT statement (selections):

CONCAT(): Concatenation
TRIM(): Allows you to clean up unnecessary character strings. By default, remove spaces before and after a character string. Advanced options: truncate specific characters before and/or after a string.
LENGTH(): Get the length of a string of characters.
LOWER(): Convert to lowercase
UPPER(): Convert to uppercase
SUBSTR(): Select a part of the string
REPLACE(): Replace a string by another one (case sensitive) - Useful to update urls on the whole site for example
SOUNDEX(): Returns the sound of a string (allows to select the values having the same sound when pronounced orally) - Rare use
REVERSE(): Invert the string of character

3. Time functions

Add and substract time intervals:

ADDDATE(): add a time interval to a date. Available INTERVAL values: YEAR, DAY, MONTH. By default, if no interval is specified but just an integer, the function adds a number of days.
SUBDATE(): subtract an interval from a date.
ADDTIME(): As ADDDATE but for TIME field types (add hours, minutes or seconds)
SUBTIME(): Subtract hours, minutes or seconds from the values contained in a TIME column.
DATEDIFF(): Returns the interval between two dates). By default, returns the number of days. To obtain a DATE, use DATEDIFF inside the FROM_DAYS function (see below).
FROM_DAYS(): Allows to return a date from an interval of days. Format: YYYYY-MM-DD (see sample code at the bottom of this page)

Dynamic values:

NOW(): Default ( noted as: NOW() ), this function retrieves a TIMESTAMP (noted in this format: YYYYY-MM-DD HH:ii:ss). Retrieves the date AND the time.
CURDATE(): retrieves only the date (not the time). Format: YYYYY-MM-DD (year-month-day).
CURTIME(): Retrieves only the time. Format: HH:ii:ss

Return part of a date / time:

DATE(): Extract the date part of a DATE or DATETIME expression
TIME(): Extract the time portion of the expression passed
YEAR(): Will return only the date from a TIMESTAMP (format: 0000-00-00 00:00:00:00) or a DATE (format: 0000-00-00-00).
MONTH() / WEEK() / DAY(): Like YEAR but returns the month of the year / week of the year / day of the month
HOUR() / MINUTE() / SECOND(): Returns the hour / minutes / seconds

Date formatting (to be managed at PHP level in general):

DATE_FORMAT(): For the list of abbreviations, click here. See an example at the bottom of the page. The names of the days and months are returned in English.
GET_FORMAT(): allows you to retrieve date formats in automatic / prefabricated ways (we won't use them because the formats are not necessarily logical and are rigid).

Code

Examples:

ADDDATE(birtday, INTERVAL 1 YEAR) 
	// Will return a new column in which values will be equled to the "birthday" column value + 1 year

SELECT * FROM users WHERE birthday < SUBDATE(NOW(), INTERVAL 16 YEAR);
	// Will select users who are 16 years old at least

SELECT *, DATEDIFF(CURDATE(), birtday) FROM users;
	// Returns a new column containing the interval of days between the current date and the "birtday" column value for each line. -> Output: "857" (for: 857 days)

SELECT *, FROM_DAYS(DATEDIFF(CURDATE(), birtday)) FROM users;
	// Output: "0002-04-07" (for: 2 years, 4 months and 7 days. Which is equivalent to 857 days)

SELECT *, DATE_FORMAT(birthday, "%W %d %M %Y") FROM users;
	// Return a new columns containing "birthday" column values with this new format: "Saturday 27 Octiber 2020".

SELECT COUNT(*) AS population,
	YEAR(birthday) AS year,
	MONTH(birthday) AS month
FROM users
GROUP BY 
	YEAR(birthday),
	MONTH(birthday)

	// Will return a table as following:
	population	year	month
	4		1980	3
	1		1981	5
	2		1982	1

▲