What are MySQL functions

MySQL is used to manage the data of web applications, websites, and even mobile applications by sorting it into tables; because of this, it is called a relational database. This popular database management system uses SQL query language to retrieve or modify the information of the database. MySQL also supports functions, which take one or more inputs and return a single particular output for a particular task. In this post, a detailed explanation is made of the functions of MySQL of both built-in functions and custom functions with the help of different examples.

What are the built-in functions in MySQL

MySQL supports many built-in functions which take one or more inputs and return a single output. In MySQL, we have different types of functions:

String functions
Numeric functions
Date functions
Advanced functions

These are explained in detail.

String functions

The functions that are used to handle the strings are known as strings functions, some examples and usage of strings functions in MySQL are:

CHAR_LENGTH(string): It is a string function that is used to calculate the length of the strings. To understand it let us consider an example, we have a table with the name, emp_data, we will find the length of its employee’s names column using the command:

SELECT emp_name, CHAR_LENGTH(emp_name) FROM emp_data;

UPPER(string): It is a string function used to convert the alphabets in the upper case letters. To understand it we again consider the previous example of the emp_data table:

SELECT emp_name, UPPER(emp_name) FROM emp_data;

SUBSTR(): It is used to subtract the characters from the string, you have to mention the starting point and the ending point of subtracting the characters. To understand it, we will take a string of “obedient” and subtract

“ient” part by using the command:

SELECT SUBSTR("Obedient", 1, 4);

In the above example, “1” and “4” digits indicated the first and fourth letters of the word “obedient”.”

Numeric functions

These functions are used to handle the numeric data, there are a lot of functions some of them are given below:

SUM(): It is used to perform the addition on the given data, to understand it, we will consider an example, we pass the data of the column of the total_price of the table, Customer_data, and calculate its sum using the function:

SELECT customer_name, SUM(total_price) FROM Customer_data GROUP BY customer_name;

MAX(): It is used to find out the maximum value from the given data of numerical, for example, in the table of Customer_data, we find out the maximum total cost using the MAX(), run the command:

SELECT customer_name, MAX(total_price) FROM Customer_data GROUP BY customer_name;

POWER(): It is used to find out the power of the integers by defining the base integer and the number by which its power is to be calculated. Let us consider an example, we will find the power of 10 by 6 using the function POWER():

SELECT POWER (10,6);

DATE Functions

These functions are used to extract or deal with the date and time in MySQL. Some examples of such functions are explained here.

CURRENT _DATE(): It is used to extract the present date in MySQL, for better understanding, run the following command:

SELECT CURRENT_DATE();

WEEKOFYEAR(): It is used to display the week of the year, we will pass the date to the function and it will tell you in which week of the year this date exists. To understand this, we are passing the present date by using the function of now():

SELECT WEEKOFYEAR(now());

CURRENT_TIMESTAMP(): It is used to display the current time and date in MySQL, let’s understand it by executing the following command:

SELECT CURRENT_TIMESTAMP();

Advanced functions

There are some other functions that are used for different functions like for the conversion of numbers to binary. Some of these functions are explained.

BIN(): It is used to convert the number into the binary, to understand the use of this function, consider the example:

SELECT BIN(76);

SYSTEM_USER(): It is used to extract the information about the system user name or hostname, for example, we run the command using the function in the MySQL:

SELECT SYSTEM_USER();

DATABASE(): It is used to extract the information about the name of the database which is being used, for example, we run the command:

SELECT DATABASE();

How to make custom function in MySQL

We can also create customized functions like other programming languages functions can be made to avoid repetition of the same code for the same purpose. In MySQL, the general syntax for making the customized function:

DELIMITER $$

CREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) ]

RETURNS return_datatype

BEGIN

declaration_section

executable_section

END$$

DELIMITER ;

The explanation of the above syntax is:

If you don’t use the DELIMITER, MySQL uses it by default
Use the clause of CREATE FUNCTION to create the function and assign some suitable name to it
Inside the brackets declare its parameters which are to be passed in the functions and also their data types
Write your code in the declaration section
Write the executable section of the code
Make sure your code is written between the BEGIN and END blocks

To understand it, we create a function of displaying current time instead of using NOW() again and again, we will call Clock():

DELIMITER $$

CREATE FUNCTION Clock()

RETURNS TIME

BEGIN

RETURN NOW();

END$$

DELIMITER ;

To call the function, we execute the command:

SELECT Clock();

Conclusion

Functions in MySQL take one or more inputs and return a single output with the specific results. MySQL is an RDMS that is enriched with not only a lot of built-in functions but also allows you to create your own custom functions as they create in other programming languages. In this post, both types of functions, built-in as well as customized are explained in detail with their examples.

What are MySQL functions | Examples