Functions
| Function | Description | Example |
|---|---|---|
| ABS(n) |
Returns the absolute value of n |
SELECT ABS(-5); returns 5 |
| LOG(n) |
Returns the natural logarithm of n |
SELECT LOG(10); returns 2.302585092994046 |
| POW(x, y) | Returns x to the power of y | SELECT POW(2, 3);
returns 8 |
| RAND() | Returns a random number between 0 (inclusive) and 1 (exclusive) | SELECT RAND();
returns 0.30263431491575604 |
| ROUND(n, d) | Returns n rounded to d decimal places | SELECT ROUND(16.25, 1);
returns 16.3 |
| SQRT(n) | Returns the square root of n | SELECT SQRT(25);
returns 5 |
| CEILING(x) | Returns the smallest integer not less than x | SELECT CEILING(5.3);
returns 6 |
| FLOOR(x) | Returns the largest integer not greater than x | SELECT FLOOR(5.9);
returns 5 |
| TRUNCATE(n, d) | Returns n truncated to d decimal places | SELECT TRUNCATE(5.967, 2);
returns 5.96 |
| Function | Description | Example |
|---|---|---|
| CONCAT(s1, s2, …) |
Returns the string that results from concatenating the string arguments |
SELECT CONCAT(‘Dis’, ‘en’, ‘gage’); returns ‘Disengage’ |
| LOWER(s) | Returns the lowercase s | SELECT LOWER(‘MySQL’);
returns ‘mysql’ |
| REPLACE(s, from, to) | Returns the string s with all occurrences of from replaced with to | SELECT(‘This and that’, ‘and’, ‘or’);
returns ‘This or that’ |
| SUBSTRING(s, pos, len) | Returns the substring from s that starts at position pos and has length len | SELECT SUBSTRING(‘Boomerang’, 1, 4);
returns ‘Boom’ |
| TRIM(s) | Returns the string s without leading and trailing spaces | SELECT TRIM(‘ test ‘);
returns ‘test’ |
| UPPER(s) | Returns the uppercase s | SELECT UPPER(‘mysql’);
returns ‘MYSQL’ |
| CHAR_LENGTH(s) | Returns the length of the string s.
CHAR_LENGTH() correctly counts characters for more character encodings, including UTF-8, than LENGTH() and is preferred. |
SELECT CHAR_LENGTH(‘BDM’);
returns 3 |
| LOCATE(sub, s) | Return the position of the first occurence of the substring sub in the string s | SELECT LOCATE(‘Data’, ‘Business Data Management’);
returns 10 |
| FORMAT(n, d) | Round the number n to have d decimal places and add commas to the integer part. | SELECT FORMAT(5423.859, 2);
returns 5,423.86 |
| Function | Description | Example |
|---|---|---|
| CURRDATE()
CURTIME() NOW() |
Returns the current date, time, or date and time in |
SELECT CURDATE();
returns ‘2019-01-25’ SELECT CURTIME();
returns ’21:05:44′ SELECT NOW();
returns ‘2019-01-25 21:05:44’ |
| DATE(expr) TIME(expr) |
Extracts the date or time from a date or datetime expression expr |
SELECT DATE('2013-03-25 22:11:45');
returns ‘2013-03-25’ SELECT TIME('2013-03-25 22:11:45');
returns ’22:11:45′ |
| DAY(d) MONTH(d) YEAR(d) |
Returns the day, month, or year from date d |
SELECT DAY('2016-10-25');
returns 25 SELECT MONTH('2016-10-25');
returns 10 SELECT YEAR('2016-10-25');
returns 2016 |
| HOUR(t) MINUTE(t) SECOND(t) |
Returns the hour, minute, or second from time t |
SELECT HOUR('22:11:45');
returns 22 SELECT MINUTE('22:11:45');
returns 11 SELECT SECOND('22:11:45');
returns 45 |
| DATEDIFF(expr1, expr2) TIMEDIFF(expr1, expr2) |
Returns expr1 – expr2 in number of days or time values, given expr1 and expr2 are date, time, or datetime values |
SELECT DATEDIFF('2013-03-10', '2013-03-04');
returns 6 SELECT TIMEDIFF('10:00:00', '09:45:30');
returns 00:14:30 |
| DATE_ADD(d, interval_expression)
|
Returns the new date after adding the interval in the interval_expression to date d. There are a variety of options. | SELECT DATE_ADD(
‘2018-05-01’, INTERVAL 1 DAY); returns ‘2018-05-02’ |
| STR_TO_DATE(s, format)
|
Returns a date type by interpreting the given format of the string s. %y is a 2 digit year and %Y is a 4 digit year. | SELECT STR_TO_DATE(’03/20/1999’,
‘%m/%d/%Y’); Returns ‘1999-03-20’ |