SQL Functions

We had a small preview of SQL functions in the last page about grouping. We saw AVG() in action. Here we are going to see the other available functions. Some of the functions may be same across different SQL databases - but many others will be different. Here is a few links to the SQL function documentation for the most popular database servers...

PostgreSQL - Find PostgreSQL's functions using \df command.
MySQL Functions
SQLite Core Functions
Oracle
SQL Functions
General SQL Functions

Generally, functions are classified into two groups - aggregate functions and single value functions(or scalar functions).

By the way, all the functions given below are for MySQL. Most of them work in other Database servers too - but some don't. Refer to the manual of the server you are using to find the functions it supports. You are warned.

Aggregate Functions

Aggregate functions are the functions that needs multiple values together to operate. It usually returns a single value. We saw this in the last page about grouping. In that example AVG() was an aggregate function. The most used aggregate functions are...

AVG(column): Returns the average value of a column
COUNT(column/*): Returns the number of rows
MAX(column): Returns the highest value of a column
MIN(column): Returns the lowest value of a column
SUM(column): Adds all the values of the given column and returns the sum.

Scalar Functions

These functions only require one value as input(instead of a whole columns or rows like in aggregate functions).

String Functions

CONCAT(str, str, str, ...): Return a concatenated string of all the values given as the argument.
FORMAT(Number, Decimal Points): Return a number formatted to specified number of decimal places. Eg. FORMAT(15.3212, 2) returns 15.32
TRIM(String): Remove leading and trailing spaces
UPPER(String): Converts the string given as argument to upper case and returns it.
LOWER(String): Converts to lower case and returns it.

Numeric Functions

CEIL(Decimal Number): Returns the decimal number rounded to the next integer. CEIL(5.2) returns 6.
FLOOR(Decimal Number): Returns the decimal number rounded to the previous integer. CEIL(5.7) returns 5.
ROUND(Number, Decimal Points): Rounds the given Number to the given Decimal points. If the second argument is not given, it returns the number rounded to the nearest integer.
POW(Number,Power): Returns Number^Power.
RAND(): Returns a random floating-point value between 0 and 1.0.

Date and Time Functions

In MySQL date are represented as 'YYYY-MM-DD' and time as 'YYYY-MM-DD HH:MM:SS'.

ADDDATE(day, INTERVAL Number DAY): You can find the future using this function. At least, you can find the date of the future. ADDDATE('2005-03-30', INTERVAL 2 DAY) Returns '2005-04-01'
DATEDIFF(day1, day2): Finds the difference between two days.
CURDATE(): Returns today's date.
DATE_FORMAT(Date, Format): Formats the given date in the specified format.
NOW(): Returns the current time.
YEAR(Date): Fetches and returns the year in the specified date
MONTH(Date): Returns the month in the given date
DAY(Date): Returns the day of the date given as the argument.

Other Functions

MD5(String): Returns the MD5 hash of the string provided as the argument.
SHA1(String): Returns the SHA1 hash of the string

Previous
Group Multiple Rows using GROUP BY

Next
Inserting Data

Contents