MySQL Functions
MySQL Numeric Functions
1. ABS: The ABS () function returns the absolute (positive) value of a number.
Syntax: ABS (number)
Parameter: number
Description. A numeric value Required
Example
Return the absolute value of a number:
SELECT ABS (-243.5);
2. AVG: The AVG () function returns the average value of an expression. Note: NULL values are
ignored.
Syntax: AVG (expression)
Parameter: expression
Description: Required. A numeric value (can be a field or a formula)
Example
i) Return the average value for the "Price" column in the "Products" table:
SELECT AVG(Price) AS Average Price FROM Products;
ii) Select the records that have a price above the average price:
SELECT * FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);
3. CEIL: The CEIL () function returns the smallest integer value that is bigger than or equal to a
number.
Syntax: CEIL (number)
Parameter: number
Description: Values
Parameter: Required. A numeric value.
Note: This function is equal to the CEILING function.
Example: Return the smallest integer value that is greater than or equal to 25.75:
SELECT CEIL (25.75);
4. COUNT() Function: The COUNT() function returns the number of records returned by a select
query.
Note: NULL values are not counted.
Syntax: COUNT (expression)
Parameter: expression
Description: Required. A field or a string value
Example: Return the number of products in the "Products" table:
SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
5. DIV Function: The DIV function is used for integer division (x is divided by y). An integer value is
returned.
Parameter Description
x Required. A value that will be divided by y
y Required. The divisor
Example: 1
Integer division (10/5):
SELECT 10 DIV 5;
Example:2
Integer division (8/3):
SELECT 8 DIV 3;
[Link] () Function: The FLOOR () function returns the largest integer value that is smaller than
or equal to a number.
Note: Also look at the ROUND (), CEIL (), CEILING (), TRUNCATE (), and DIV functions.
Parameter: number
Description: Required. A numeric value
Example: Return the largest integer value that is less than or equal to 25.75:
SELECT FLOOR (25.75);
[Link] () Function: The GREATEST () function returns the greatest value of the list of
arguments.
Note: See also the LEAST () function.
Syntax
GREATEST (arg1, arg2, arg3, ...)
Parameter Values
Parameter Description
arg1, arg2, arg3, Required. The list of arguments to be evaluated
...
Example
Return the greatest value of the list of arguments:
SELECT GREATEST (3, 12, 34, 8, 25);
[Link] () Function: The LEAST () function returns the smallest value of the list of arguments.
Note: See also the GREATEST () function.
Syntax: LEAST (arg1, arg2, arg3, ...)
Parameter Values
Parameter Description
arg1, arg2, arg3, ... Required. The list of arguments to be evaluated
Example:
Return the smallest value of the list of arguments:
SELECT LEAST (3, 12, 34, 8, 25);
[Link] () Function: The MAX () function returns the maximum value in a set of values.
Note: See also the MIN () function.
Syntax:
MAX (expression)
Parameter Values
Parameter Description
expression Required. A numeric value (can be a field or a formula)
Example: Find the price of the most expensive product in the "Products" table:
SELECT MAX(Price) AS LargestPrice FROM Products;
[Link] () Function
The MOD () function returns the remainder of a number divided by another number.
Syntax:
MOD (x, y)
OR:
x MOD y
OR:
x%y
Parameter Values
Parameter Description
x Required. A value that will be divided by y
y Required. The divisor
Example
Return the remainder of 18/4:
SELECT MOD (18, 4);
[Link] () Function: The POW () function returns the value of a number raised to the power of
another number.
Note: This function is equal to the POWER () function.
Syntax
POW (x, y)
Parameter Values
Parameter Description
x Required. A number (the base)
y Required. A number (the exponent)
Example
Return 8 raised to the third power:
SELECT POW (8, 3);
[Link] () Function: The ROUND () function rounds a number to a specified number of decimal
places.
Note: See also the FLOOR(), CEIL(), CEILING(), and TRUNCATE() functions.
Syntax
ROUND (number, decimals)
Parameter Values
Parameter Description
number Required. The number to be rounded
decimals Optional. The number of decimal places to round number to. If omitted, it
returns the integer (no decimals)
Example
Round the number to 2 decimal places:
SELECT ROUND (135.375, 2);
Example
Round the Price column (to 1 decimal) in the "Products" table:
SELECT ProductName, Price, ROUND (Price, 1) AS Rounded Price FROM Products;
[Link]() Function The SUM() function calculates the sum of a set of values.
Note: NULL values are ignored.
Syntax
SUM(expression)
Parameter Values
Parameter Description
expression Required. A field or a formula
[Link] () Function The TRUNCATE () function truncates a number to the specified
number of decimal places.
Note: See also the FLOOR (), CEIL (), CEILING (), and ROUND () functions.
Syntax
TRUNCATE (number, decimals)
Parameter Values
Parameter Description
number Required. The number to be truncated
decimals Required. The number of decimal places to truncate to
Example 1:
Return a number truncated to 2 decimal places:
SELECT TRUNCATE (135.375, 2);
Example 2
Return a number truncated to 0 decimal places:
SELECT TRUNCATE (345.156, 0);
MySQL String Functions
[Link] ASCII () function returns the ASCII value for the specific character.
Syntax
ASCII(character)
Parameter Values
Parameter Description
character Required. The character to return the ASCII value for. If more than one
character is entered, it will only return the value for the first character
Example 1
Return the ASCII value of the first character in "CustomerName":
SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers;
2.CHAR_LENGTH () Function
The CHAR_LENGTH () function return the length of a string (in characters).
Note: This function is equal to the CHARACTER_LENGTH () function.
Example
Return the length of the text in the "CustomerName" column:
SELECT CHAR_LENGTH(CustomerName) AS LengthOfName FROM Customers;
3. CONCAT() Function
The CONCAT () function adds two or more expressions together.
Note: Also look at the CONCAT_WS () function.
Syntax
CONCAT(expression1, expression2, expression3,...)
Parameter Values
Parameter Description
expression1, Required. The expressions to add together.
expression2,
expression3, Note: If any of the expressions is a NULL value, it returns NULL
etc.
Example
Add three columns into one "Address" column:
SELECT CONCAT(Address, " ", PostalCode, " ", City) AS Address FROM Customers;
4. LCASE () Function
The LCASE () function converts a string to lower-case.
Note: The LOWER () function is a synonym for the LCASE () function.