Simple Functions in SQL
A function operates on an expression enclosed in parentheses, called an argument, and returns a value.
- can have multiple arguments, separated by commas
- operates on and evaluates to specific data types
- can appear in
SELECTclause orWHEREclause
Numeric Functions
- numeric functions operate on, and evaluate to, integer and decimal data types
Common Numeric Functions
Function Description Example ABS(n) Returns the absolute value of n SELECT ABS(-5);
returns 5LOG(n) Returns the natural logarithm of n SELECT LOG(10);
returns 2.302585092994046POW(x, y) Returns x to the power of y SELECT POW(2, 3);
returns 8RAND() Returns a random number between 0 (inclusive) and 1 (exclusive) SELECT RAND();
returns 0.11831825703225868ROUND(n, d) Returns n rounded to d decimal places SELECT ROUND(16.25, 1);
returns 16.3SQRT(n) Returns the square root of n SELECT SQRT(25);
returns 5
String Functions
String functions manipulate string values.
Common String Functions
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 REPLACE('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’
Date and Time Functions
Date and time functions operate on DATE, TIME, and DATETIME data types.
Common Date and Time Functions
Function Description Example CURDATE()
CURTIME()
NOW()Returns the current date, time, or date and time in 'YYYY-MM-DD','HH:MM:SS', or'YYYY-MM-DD HH:MM:SS'formatSELECT 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 exprSELECT 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 25SELECT MONTH('2016-10-25');
returns 10SELECT YEAR('2016-10-25');
returns 2016HOUR(t)
MINUTE(t)
SECOND(t)Returns the hour, minute, or second from time t SELECT HOUR('22:11:45');
returns 22SELECT MINUTE('22:11:45');
returns 11SELECT SECOND('22:11:45');
returns 45DATEDIFF(expr1, expr2)
TIMEDIFF(expr1, expr2)Returns expr1 - expr2 in number of days or time
values, given expr1 and expr2 are date, time, or datetime valuesSELECT DATEDIFF('2013-03-10', '2013-03-04');
returns 6SELECT TIMEDIFF('10:00:00', '09:45:30');
returns 00:14:30