EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Function
Secondary Sidebar
MySQL Tutorial
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL?Table?Size
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

MySQL Function

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.

MySQL Function

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.
  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 which 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

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;

MySQL Function Character

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;

MySQL Function CHAR LENGTH

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;

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

MySQL Function - substr

e. strcmp(): It compares string 1 and string 2, and the compared value is counted.

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

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;

Addition operation

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

ABS(-222.8)

MySQL Function - ABS()

c. floor(): The biggest integer value that is less than or equal to a given number is returned.

floor(3.84)

floor()

d. ceiling(): The smallest integer value larger than or equal to a given number is returned.

ceiling(3.67)

MySQL Function - ceiling()

e. Round(): A number that has been 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 Values.

select truncate(1245.788,2);

MySQL Function - truncate

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

  1. MySQL Fetch Array
  2. MySQL rename database
  3. MySQL DROP TABLE
  4. MySQL FORMAT
Popular Course in this category
MS SQL Training (16 Courses, 11+ Projects)
  16 Online Courses |  11 Hands-on Projects |  70+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle Training (17 Courses, 8+ Projects)4.9
PL SQL Training (4 Courses, 2+ Projects)4.8
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*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?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more