Updated June 1, 2023
Introduction to MySQL Function
A function in MySQL is a saved program where we can pass parameters and get a result. There are several built-in functions in MySQL. You can substitute a stored function for an expression in SQL statements whenever needed. The procedural code is now simpler to understand and maintain as a result.
Key Takeaways
- We can expand the capabilities of MySQL by using functions.
- Functions may accept parameters and would always return a value.
- Built-in functions are those that come with MySQL by default. They can be divided into three categories based on the data they work with strings, dates, and built-in functions for numbers.
- In the MySQL server, stored functions are generated by the user and can be utilized in SQL queries.
Overview of MySQL Functions
Simply put, functions are segments of code that carry out specific tasks and produce an outcome. Other functions do not accept parameters, whereas some functions do. MySQL allows functions that take one or more inputs and produce just one specific result for a particular task.
All MySQL Functions
Built-in Functions: Several built-in functions are included with MySQL. Built-in functions are merely those that the MySQL server comes with already developed. We may manipulate the data in several ways.
You can employ the following popular categories to classify the built-in features broadly.
- Operate on string data types with string functions.
- On numerical data types, numerical functions can be used.
- You can use data types and date functions to work with dates in MySQL.
- On all of the data above types, aggregate functions can be used.
- To explain all the functions in SQL, Firstly, I need to create a database and a Specific table to go with.
- The database I created here is educorp.
- The table I m going to use throughout this article is blogs except for a Stored Function.
Let’s see different Functions in SQL with the Example.
1. String Functions
MySQL string methods make manipulating character string data possible because it enables users to alter data strings and query details regarding a string returned by a SELECT query.
String values are essentially just pieces of text that we can alter before using, like:
- Adding text to a variable already present.
- Modifying a string’s portion.
- Finding a text fragment within a string.
Following are a few String Functions:
a. ASCII: The ASCII() function returns the leftmost character of the provided str string’s ASCII (numeric) value. In the absence of a str argument, the method returns 0. If str is NULL, returns NULL.
Select title, ASCII (title) AS NumCodeOfFirstChar from blogs;
b. char_length(): You can use the string function CHAR_LENGTH(string) to determine the length of strings. It gives the string’s size in characters for the string str. Both char and length functions return the length of the argument, which is a string. But they serve different purposes. While LENGTH() returns the length in bytes, CHAR LENGTH() returns the length of the string as the number of characters included. To understand it better, let’s look at an example.
Select blogname, CHAR_LENGTH(blohname) from blogs;
c. UPPER: You can use the string function UPPER(string) to convert alphabet letters into uppercase characters. It returns a field into an Uppercase Character. For Example, java is turned into JAVA.
Select title, UPPER(title) from blogs;
d. substr: SUBSTR() is used to remove characters from a string; we must specify the beginning and ending points of the character removal. The fundamental syntax first specifies the parameter, followed by the substring’s position or the character from which the string starts. Since counting begins on the left, the position is an integer.
select SUBSTR("entrepreneur" , 1, 4); 
e. strcmp(): It compares string 1 and string 2 and counts the resulting compared value.
select STRCMP("java_sss", "java_s");
f. replace(): The REPLACE() function substitutes another character for one or even more text characters (s). As a result, one can specify the string, the character to be substituted, and the character to be replaced using the function.
select REPLACE("HELLO WORLD", "WORLD", "php");
2. Numeric Operations
Here, you will find a variety of functions for managing numerical data. Mysql function can perform simple math properties using mathematical operators and return the numbers.
a. Addition operation: It is performed using operators.
Select 6+4;
b. ABS(): It returns the absolute value of a number.
ABS(-222.8)
c. floor(): The function returns the largest integer value that is less than or equal to a given number.
floor(3.84)
d. ceiling(): The function returns the smallest integer value greater than or equal to a given number.
ceiling(3.67)
e. Round(): A number rounded to a specific number of decimal places is returned.
Round(14.5677,2)
3. Aggregate Functions
An aggregate function in database administration is a procedure that groups the values from various rows as input according to specific requirements to create a single value with more deep meaning.
a. SUM: It adds the specific values in a column.
select title, SUM(amount) from blogs GROUP BY blogname;
b. power(): Finds the power of a given number. Raised to power.
select power(20,6);
c. COUNT: Gives the No.of records.
select COUNT(title) AS blogcount from blogs;
d. MIN: Finds the minimal value in the field.
select MIN(amount) AS small from blogs;
e. MAX: Finds the Maximum value in the field.
select MAX(amount) AS BIG from blogs;
f. truncate: It truncates a given decimal Value.
select truncate(1245.788,2);
Function Using Stored Procedure
To create it, use the CREATE keyword, followed by the function name, and end it with the DELIMITER keyword. In MySQL, a stored function is a group of SQL statements that carry out a single action or task and return a single value. It comes under one of MySQL’s categories of stored programs.
The syntax for CREATE FUNCTION statement in Mysql is:
CREATE FUNCTION funct_name (parameter1, parameter2, ..)
RETURN datatype
Body of a FunctionCode:
CREATE FUNCTION parent_Occupation(
age int
)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE parent_occupation VARCHAR(20);
IF age > 36 THEN
SET parent_occupation = 'Enterpreneur';
ELSEIF (age <= 35 AND
age >= 25) THEN
SET parent_occupation = 'Engineer';
ELSEIF age < 30 THEN
SET parent_occupation = 'Actor';
END IF;
-- return the parent occupation
RETURN (parent_occupation);
END$$
Conclusion – MySQL Function
As a result, I have described and demonstrated how to utilize several of the most popular and helpful MySQL functions in this post.
Recommended Articles
We hope that this EDUCBA information on “MySQL Function” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
