Introduction to MySQL Function
A function in MySQL is a saved program into within which we can pass parameters and get a result. There are several built-in functions in MySQL. Whenever an expression is used in SQL statements, a stored function may be substituted. 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 sorts of 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 query.
Overview of MySQL Functions
Simply put, functions are segments of code that carry out certain tasks and then produce an outcome. Other functions do not accept parameters, whereas some functions do. MySQL allows functions that accept one or more inputs and produce just one specific result for a certain 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.
The following most popular categories can be employed to broadly classify the built-in features.
- Operate on string data types with string functions.
- On numerical data types, numerical functions can be used.
- On date data types, date functions can be used.
- On all of the aforementioned data 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 which 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
Effective manipulation of character string data is made possible by MySQL string methods. Because it enables users to alter data strings and query details regarding a string that was 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(): The string function CHAR LENGTH(string) is used to figure out 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: The string function UPPER(string) is used to change alphabet letters in upper case 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 is to first specify 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 the compared value is counted.
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
There are numerous functions provided here that are used to manage 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 biggest integer value that is less than or equal to a given number is returned.
floor(3.84)
d. ceiling(): The smallest integer value larger than or equal to a given number is returned.
ceiling(3.67)
e. Round(): A number that has been 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 Values.
select truncate(1245.788,2);
Function Using Stored Procedure
It is created using a CREATE followed by a Function name and Ends with the DELIMITER. 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 a number of the most popular and helpful MySQL functions in this post.
Recommended Articles
This is a guide to MySQL Function. Here we discuss the introduction, all MySQL functions, and functions using stored procedure. You can also look at the following articles to learn more –
