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 SELECT clause or WHERE clause

Numeric Functions

  • numeric functions operate on, and evaluate to, integer and decimal data types

Common Numeric Functions

FunctionDescriptionExample
ABS(n)Returns the absolute value of nSELECT ABS(-5);


returns 5
LOG(n)Returns the natural logarithm of nSELECT LOG(10);


returns 2.302585092994046
POW(x, y)Returns x to the power of ySELECT POW(2, 3);


returns 8
RAND()Returns a random number between 0 (inclusive) and 1 (exclusive)SELECT RAND();


returns 0.11831825703225868
ROUND(n, d)Returns n rounded to d decimal placesSELECT ROUND(16.25, 1);


returns 16.3
SQRT(n)Returns the square root of nSELECT SQRT(25);


returns 5

String Functions

String functions manipulate string values.

Common String Functions

FunctionDescriptionExample
CONCAT(s1, s2, …)Returns the string that results from concatenating the string argumentsSELECT CONCAT('Dis', 'en', 'gage');


returns ‘Disengage’
LOWER(s)Returns the lowercase sSELECT LOWER('MySQL');


returns ‘mysql’
REPLACE(s, from, to)Returns the string s with all occurrences of from replaced with toSELECT 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 lenSELECT SUBSTRING('Boomerang', 1, 4);


returns ‘Boom’
TRIM(s)Returns the string s without leading and trailing spacesSELECT TRIM(' test ');


returns ‘test’
UPPER(s)Returns the uppercase sSELECT 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

FunctionDescriptionExample
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' format
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 dSELECT 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 tSELECT 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