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:
These are explained in detail.
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:
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:
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:
In the above example, “1” and “4” digits indicated the first and fourth letters of the word “obedient”.”
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:
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:
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():
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:
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():
CURRENT_TIMESTAMP(): It is used to display the current time and date in MySQL, let’s understand it by executing the following command:
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:
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:
DATABASE(): It is used to extract the information about the name of the database which is being used, for example, we run the command:
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:
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():
To call the function, we execute the command:
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.