EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Function
 

MySQL Function

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.

 

 

MySQL Function

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.
  1. To explain all the functions in SQL, Firstly, I need to create a database and a Specific table to go with.
  2. The database I created here is educorp.
  3. The table I m going to use throughout this article is blogs except for a Stored Function.

MySQL Function Table

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;

MySQL Function Character

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;

MySQL Function CHAR LENGTH

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;

MySQL Function - Upper

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);

MySQL Function - substr

e. strcmp(): It compares string 1 and string 2 and counts the resulting compared value.

select STRCMP("java_sss", "java_s");

MySQL Function - strcmp()

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");

MySQL Function - replace()

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;

Addition operation

b. ABS(): It returns the absolute value of a number.

ABS(-222.8)

MySQL Function - ABS()

c. floor(): The function returns the largest integer value that is less than or equal to a given number.

floor(3.84)

floor()

d. ceiling(): The function returns the smallest integer value greater than or equal to a given number.

ceiling(3.67)

MySQL Function - ceiling()

e. Round(): A number rounded to a specific number of decimal places is returned.

Round(14.5677,2)

Round()

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;

MySQL Function - Round()

b. power(): Finds the power of a given number. Raised to power.

select power(20,6);

power()

c. COUNT: Gives the No.of records.

select COUNT(title) AS blogcount from blogs;

MySQL Function COUNT

d. MIN: Finds the minimal value in the field.

select MIN(amount) AS small from blogs;

MIN

e. MAX: Finds the Maximum value in the field.

select MAX(amount) AS BIG from blogs;

MAX

f. truncate: It truncates a given decimal Value.

select truncate(1245.788,2);

MySQL Function - truncate

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 Function

Code:

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$$

MySQL CREATE FUNCTION

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.

  1. MySQL Fetch Array
  2. rename a database in MySQL
  3. MySQL DROP TABLE
  4. MySQL FORMAT

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - ENROLL NOW