Post
Ask Question

MySQL Functions

Introduction

 
In this tutorial, I shall explain some important functions in MySQL. And, I think every developer and DBA should have an understanding of these MySQL Functions. These functions are very useful for MySQL users. And, knowing these MySQL functions can help to solve some complex tasks and be used in many situations.
 
The functions allow us to improve the functionality of MySQL. Functions also return a value when applying on a dataset and accept the parameters as an option. MySQL always comes with some built-in functions. They can be classified depending on the usage and datatypes, i.e. Strings, Comparison, Aggregate, DateTime, and Numeric built-in functions.
 
This tutorial will show you various MySQL Functions including String, Aggregate, Maths, DateTime, and Comparison Functions. This detailed article will cover the following topics as follows.
  • Introduction to MySQL Functions
  • MySQL String Functions
  • MySQL Date Functions
  • MySQL Aggregate Functions
  • MySQL Comparison Functions
  • MySQL Maths Functions
  • Conclusion
First, let's create a database with a few tables containing some dummy data. Here, I am providing the database with the tables containing the records, on which I am showing you the various examples. Let's see.
  1. CREATE DATABASE MySQLFunctions;   
  1. USE MySQLFunctions;  
  2.    
  3. CREATE TABLE StudentDetails (  
  4. StudentNumber INT NOT NULL,  
  5. StudentName VARCHAR(50) NOT NULL,  
  6. contactLastName VARCHAR(50) NOT NULL,  
  7. contactFirstName VARCHAR(50) NOT NULL,  
  8. contactnumber VARCHAR(50) NOT NULL,  
  9. addressLine1 VARCHAR(250) NOT NULL,  
  10. addressLine2 VARCHAR(250) DEFAULT NULL,  
  11. city VARCHAR(50) NOT NULL,  
  12. state VARCHAR(50) DEFAULT NULL,  
  13. postalCode VARCHAR(15) DEFAULT NULL,  
  14. country VARCHAR(50) NOT NULL,  
  15. PRIMARY KEY (StudentNumber)  
  16. );  
  17.    
  18. CREATE TABLE Library (  
  19. BookNumber INT NOT NULL,  
  20. BookCode VARCHAR(15) NOT NULL,  
  21. BookIssue INT NOT NULL,  
  22. CostEach DECIMAL(10,2) NOT NULL,  
  23. PRIMARY KEY (BookCode)  
  24. );  
  25.    
  26. CREATE TABLE Bookorder (  
  27. BookNumber INT NOT NULL,  
  28. orderDate datetime NOT NULL,  
  29. shippedDate DATE DEFAULT NULL,  
  30. Status VARCHAR(50),  
  31. OrderModifiedDate datetime,  
  32. PRIMARY KEY (BookNumber)  
  33. );  

A) MySQL String Functions

 
MySQL String Functions are used to handle the character string data very efficiently. Some of the String Functions are ASCII, CONCAT, INSTR, LENGHT, LTRIM, RTRIM, LEFT, RIGHT, LOWER, UPPER, SUBSTRING. Without wasting time, let's see some common string functions with examples.
 

ASCII

 
This function returns the ASCII code value of the leftmost character of the String “str” but returns 0 if the “str” is the empty string. And, it returns NULL if “str” is NULL.
 
Syntax
 
ASCII (String);
 
Example
  1. SELECT ASCII('0'),  
  2.        ASCII('A'),  
  3.        ASCII('a'),  
  4.        ASCII('Onkar'),  
  5.        ASCII(''),  
  6.        ASCII(NULL);  
ASCII
 

CONCAT

 
The MySQL, CONCAT function takes and concatenates one or more array arguments into a single array or string. The CONCAT function needs one parameter, otherwise, it will cause an error.
 
Syntax
 
CONCAT(string1, string2, ...);
 
Example 1
  1. SELECT CONCAT('In', 'd', 'ia') AS Name,  
  2.        CONCAT('V', 'A', 'T', 'S', 'A') as Name,  
  3.        CONCAT('my', NULL, 'ql') as Name,  
  4.        CONCAT(10, 3) as String; 
CONCAT
 
Example 2
  1. SELECT CONCAT(contactfirstname, ' ', contactlastname) AS StudentFUllName,  
  2. CONCAT(addressLine1, ' ', addressLine2) AS CompleteAddress  
  3. FROM StudentDetails;  
CONCAT1
 

INSTR

 
The MySQL, INSTR function is used to get the position of the first character of a substring "substr" in the main string "str". If the "substr" is not found in the main string "str", the MySQL INSTR function will return the zero value.
 
Syntax
 
INSTR(str, substr);
 
Example 1
  1. SELECT INSTR('VATSA', 'A'),  
  2.        INSTR('VATSA', 'O'),  
  3.        INSTR('VATSA', NULL);  
INSTR
 
Example 2
  1. SELECT StudentName, addressLine1  
  2. FROM StudentDetails  
  3. WHERE INSTR(addressline1,'Vatsa Colony');  
INSTR1
 

LTRIM

 
In MySQL, the LTRIM function is used to take a string and return an updated string with all the leading space characters deleted from the main string.
 
Syntax
 
LTRIM(str);
 
Example
  1. SELECT LTRIM(' helloindia') AS String,  
  2.        LTRIM(' VATSA ') AS String,  
  3.        LTRIM('H E L L O ! V A T S A...') AS String;   
LTRIM
 

RTRIM

 
In MySQL, the RTRIM function is used to take a string and return an updated string with all the trailing space characters deleted from the main string.
 
Syntax
 
RTRIM(str);
 
Example
  1. SELECT RTRIM(' Hello!Vatsa '),  
  2.        RTRIM('Hello! Vatsa        '),  
  3.        RTRIM(' B S R ');  
RTRIM
 

LOWER

 
In MySQL, the LOWER(string) function is used to return the string in the form of lowercase.
 
Syntax
 
LOWER(string);
 
Example
  1. SELECT LOWER('HELLO!INDIA...'),  
  2.        LOWER('VATSA'),  
  3.        LOWER('Hello!Vatsa...');  
LOWER
 

UPPER

 
In MySQL, the UPPER() function is used to return the uppercase value of the string.
 
Syntax
 
UPPER(string);
 
Example
  1. SELECT UPPER('helloIndia'),  
  2.        UPPER('vatsa'),  
  3.        UPPER('Hello! Vatsa...');  
UPPER
 

SUBSTRING

 
In MySQL, the function SUBSTRING(string, position) or SUBSTRING(string FROM position) returns a substring from the main string that is started at a certain position, i.e, positive or negative index. Let's understand the concept of a position in a string with the help of the following below figure.
 
SUBSTRING Concept
 
Syntax
 
SUBSTRING(string, position);
SUBSTRING(string FROM position);
 
Example
  1. SELECT SUBSTRING('Hello Vatsa', 7) AS Result,  
  2.        SUBSTRING('Hello Vatas', -6) AS Result, 
  3.        SUBSTRING('Hello India' FROM 7) AS Result,  
  4.        SUBSTRING('Hello India' FROM 7) AS Result,  
  5.        SUBSTRING('Hello India', 1, 5) AS Result,  
  6.        SUBSTRING('Hello Vatsa', -5, 5) AS Result;  
SUBSTRING
 

FORMAT

 
In MySQL, the FORMAT(N, D) function describes that the number "N" is rounded to the decimal places to "D" then, returns the string as a result. But, if the value of D is 0 then, the results don’t have a fractional part.
 
Syntax
 
FORMAT(N, D);
 
Example 1
  1. SELECT FORMAT(1235.14687, 5) AS FormatResult,    
  2.        FORMAT(1235.14687, 4) AS FormatResult,    
  3.        FORMAT(1235.14687, 3) AS FormatResult,    
  4.        FORMAT(1235.14687, 2) AS FormatResult,    
  5.        FORMAT(1235.14687, 1) AS FormatResult,    
  6.        FORMAT(1235.14687, 0) AS FormatResult;     
FORMAT1
 
Example 2
  1. SELECT BookNumber, FORMAT(BookIssue * costeach, 2) AS TotalCost  
  2. FROM Library;  
FORMAT2
 
 
To learn more about "String Functions In MySQL" in detail, read the following article "String Functions in MySQL".
 

B) MySQL Date Functions

 
In MySQL, the Date Function is used to handle Date and Time very efficiently. Some of the Date Functions are CURDATE, DATEDIFF, NOW, DAYNAME, DAYOFWEEK, MONTH, YEAR. Let's discuss some commonly used Date and Time Functions one by one.
 

CURDATE

 
In MySQL, CURDATE function is used to get the current date in the format of "YYYY-MM-DD". This means, it only returns the date. Note that, you can use CURRENT_DATE() or CURRENT_DATE in place of CURDATE(), as they are synonyms of CURDATE() function.
 
Syntax
 
CURDATE();
 
Example
  1. SELECT CURDATE(),  
  2.       CURRENT_DATE,  
  3.       CURRENT_DATE();  
CURDATE
 

DATEDIFF

 
In MySQL, the DATEDIFF function is used to get the difference between two dates or DateTime. This means it is used to calculate the difference between two dates.
 
Syntax
 
DATEDIFF(date1, date2);
 
Example 1
  1. SELECT DATEDIFF('2020-10-31','2020-10-03') AS Days_Left_To_Complete_this_Month,  
  2.        DATEDIFF('2020-11-30','2020-10-03') AS Days_Left_To_Complete_next_Month;  
DATEDIFF1
 
Example 2
  1. SELECT BookNumber, DATEDIFF(shippeddate, orderdate) AS Time_taken_To_Delivered_the_Book  
  2. FROM BookOrder;  
DATEDIFF2
 

NOW

 
In MySQL, the NOW() function is used to get the current date and time as a string or a number in the "YYYY-MM-DD HH:MM:SS" format. This means it returns the current date and time in the configured time zone.
 
Syntax
 
NOW();
 
Example
  1. SELECT NOW();  
NOW
 

DAYNAME

 
In MySQL, DAYNAME function is used to return the day on a particular date.
 
Syntax
 
DAYNAME(date);
 
Example 1
  1. SELECT DAYNAME("2020-10-03") AS Day,  
  2.        DAYNAME("2020-10-12") AS Day,  
  3.        DAYNAME("1997-01-01") AS Day,  
  4.        DAYNAME("1999-01-01") AS Day,  
  5.        DAYNAME("2020-11-01") AS Day;  
DAYNAME
 
Example 2
  1. SELECT BookNumber, dayname(shippeddate) AS Delivered_on_Day  
  2. FROM BookOrder;  
DAYNAME2
 

MONTH

 
MONTH function is used to return an integer value from a particular date which represents the month for the particular date.
 
Syntax
 
MONTH(date);
 
Example
  1. SELECT MONTH("2020-10-03"),  
  2.        MONTH(NOW()),  
  3.        MONTH("2020-11-03");  
MONTH
 

C) MySQL Aggregate Functions

 
In MySQL, Aggregate Functions are used to perform a calculation on a set of values in a table and will give a single value to the user. Aggregate functions do not handle NULL values. Aggregate Function performs the operations on a set of values. Some of the Aggregate Functions are SUM, AVG, COUNT, MAX, and MIN.
 
The user can use aggregate functions with the GROUP BY clause to fetch the data into groups. But, without a GROUP BY clause, the aggregate function gives the result into the summary form. And, with the GROUP BY clause, an aggregate function will give the result in the summary form based on the group. Without wasting time, let's discuss the most common examples of aggregate functions.
 
Syntax
 
Function_Name (DISTINCT | ALL Expresion)
 

MAX() Function

 
In MySQL, the MAX function is used to get the maximum value in a column from a table.
  
Syntax
 
SELECT MAX <Column_name>
FROM <table_name>;
 
Example
  1. SELECT MAX(Bookissue) AS MaxBookIssued  
  2. FROM library; 
MAX
 
Note:
You can use MAX() aggregate function with the "GROUP BY" clause to get the result in a group form.
 
Syntax
 
SELECT col_name, MAX(col_name)
FROM <table_name>
GROUP BY col_name;
 
Example
  1. SELECT BookNumber, MAX(Bookissue) AS MaxBookIssued  
  2. FROM library  
  3. GROUP BY BookNumber;  
MAX1
 

MIN() Function

 
MIN function returns the minimum value from a table.
 
Syntax
 
SELECT MIN(Column_name)
FROM <table_name>;
 
Example
  1. SELECT MIN(Bookissue) AS MinBookIssued  
  2. FROM library;  
MIN
 
Note:
You can use MIN() aggregate function with the "GROUP BY" clause to get the result in a group form.
 
Syntax
 
SELECT col_name, MIN(col_name)
FROM <table_name>
GROUP BY col_name;
 
Example
  1. SELECT BookNumber, MIN(Bookissue) AS MinBookIssued  
  2. FROM library  
  3. GROUP BY BookNumber;  
MIN1
 

AVG() Function

 
AVG() function is used to calculate the average value and it also ignores the NULL value.
  
Syntax
 
SELECT AVG(Column_name)
FROM <table_name>;
 
Example
  1. SELECT AVG(BookIssue*CostEach) AS AverageCosta  
  2. FROM library;  
AVG
 
Note:
You can use AVG() aggregate function with the "GROUP BY" clause to get the result in a group form.
 
Syntax
 
SELECT col_name, AVG(col_name)
FROM <table_name>
GROUP BY col_name;
 
Example
  1. SELECT BookNumber, AVG(BookIssue*CostEach) AS AverageCost  
  2. FROM library  
  3. GROUP BY BookNumber;  
AVG1
 

SUM() Function

 
SUM function returns the sum of all values in an expression. And, the SUM() functions ignore NULL values.
 
Syntax
 
SELECT SUM(Column_name)
FROM <table_name>;
 
Example
  1. SELECT SUM(BookIssue*CostEach) AS TotalCost  
  2. FROM library;  
SUM
 
Note:
You can use the SUM() aggregate function with the "GROUP BY" clause to get the result in a group form.
 
Syntax
 
SELECT col_name, SUM(col_name)
FROM <table_name>
GROUP BY col_name;
 
Example
  1. SELECT SUM(BookIssue*CostEach) AS TotalCost  
  2. FROM library;  
SUM1
 
For more details, read the following articles in which, I have described "Aggregate Functions" in detail in the article on the topic, "MySQL Aggregate Functions".
 

D) MySQL Comparison Functions

 
Some of the MySQL Comparison Functions are COALESCE, GREATEST & LEAST, ISNULL. Let's discuss them, one by one.
 

GREATEST & LEAST

 
This function is used to find out the greatest and smallest value from two or more fields respectively.
 
Syntax
 
For Greatest
GREATEST(value1, value2, value3, ...);
 
For Least
LEAST(value1, value2, value3, ...);
 
Note:
 
1) Both the functions return "NULL" if they having any "NULL" value in them.
 
2) If arguments contain the integer values and string values, then it compares them as numbers.
 
Example
  1. SELECT GREATEST(80, 90, 100),       -- 100  
  2.        LEAST(100, 90, 80),          -- 80  
  3.        GREATEST(555, null, 101),    -- null  
  4.        LEAST(101, null , 988);      -- null  
COMPARISON FUNCTIONS
 

ISNULL

 
In MYSQL, ISNULL function is used in queries to test whether the argument is true or not. It takes one argument and returns 1 if the argument is NULL, or if the argument is not NULL, then it will return 0 value.
 
Syntax
 
ISNULL(expression);
 
Example
  1. SELECT ISNULL(1),          -- 0  
  2.        ISNULL('Yes'),       -- 0  
  3.        ISNULL('Vatsa'),     -- 0  
  4.        ISNULL(100 + NULL),  -- 1  
  5.        ISNULL(NULL);        -- 1  
ISNULL COMPARISON FUNCTION
 

E) MySQL Maths Functions

 
Some of the MySQL Maths Functions in MySQL are CEIL(), FLOOR(), MOD(), ROUND(), TRUNCATE(), PI(), POW(). Let's discuss the most commonly used maths functions, one by one.
 

CEIL()

 
In MySQL, CEIL() or CEILING() function is used to return the smallest integer value which is greater than or equal to that number.
 
Syntax
 
CEIL() or CEILING();
 
Example 1
  1. SELECT CEIL(1.55),  
  2.       CEIL(1.56),  
  3.       CEIL(1.54),  
  4.       CEIL(1.01),  
  5.       CEIL(10.21),   
  6.       CEIL(100.01);  
CEIL
 
Example 2
  1. SELECT BookCode, CEILING(BookISsue * CostEach) AS TotalCost
  2. FROM Library;  
CEIL1
 

FLOOR()

 
In MySQL, the FLOOR() function is used to return the largest integer value which is smaller than or equal to that number.
 
Syntax
 
FLOOR();
 
Example 1
  1. SELECT FLOOR(10.50),  
  2.       FLOOR(2000.50),  
  3.       FLOOR(-10.20),  
  4.       FLOOR(10.98),  
  5.       FLOOR(2201.11); 
FLOOR
 
Example 2
  1. SELECT BookCode, FLOOR(BookISsue * CostEach) AS TotalCost
  2. FROM Library;  
FLOOR1
 

ROUND()

 
In MySQL, the ROUND() function is used to round a number to the mentioned number of decimal places.
  
Syntax
 
ROUND(n, [d]);
  
Example
  1. SELECT ROUND(10.49),  
  2.       ROUND(10.50),  
  3.       ROUND(10.51),  
  4.       ROUND(-10.5),  
  5.       ROUND(10.01);  
ROUND
 

Conclusion

 
In this article, we have discussed various MySQL Functions in detail with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.
  
Thanks for reading.


Similar Articles