Functions in sql

Functions in sql

What features?

MySQL can do much more than just store and retrieve data . We may also perform manipulations on data before it is retrieved or stored. This is where MySQL features come in. Functions are simply pieces of code that perform some operation and then return a result. Some functions take parameters while other functions do not take parameters.

 Let's take a quick look at an example of a MySQL function. By default, MySQL stores date data types in the "YYYY-MM-DD" format. Suppose we have created an application and our users want the date to be returned in the format "DD-MM-YYYY", for this we can use the built-in MySQL DATE_FORMAT function. DATE_FORMAT is one of the most used functions in MySQL. We will look at this in more detail when we unfold the lesson.


Why use functions?

MySQL Functions: String, Numeric, User Defined, Stored

Based on the example given in the introduction, people with experience in computer programming might be thinking, “Why bother with MySQL functions? Can the same effect be achieved with a scripting/programming language?”  It's true, we can achieve this by writing some procedures/functions in the application program.

Returning to our DATE example in the introduction, in order for our users to get the data in the right format, the business layer will need to do the necessary processing.

This becomes a problem when the application needs to integrate with other systems. When we use MySQL functions such as DATE_FORMAT we can embed this function into the database and any application that needs the data gets it in the required format. This reduces business logic rework and reduces data inconsistencies.

Another reason we should consider using MySQL features is that it can help reduce network traffic in client-server applications . The business layer should only make calls to stored functions without the need to manipulate the data. On average, using the features can significantly improve overall system performance.


Function types

Built-in Functions

MySQL comes bundled with a number of built-in functions. Built-in functions are simply functions already implemented in the MySQL server. These functions allow us to perform various types of data manipulation. Built-in functions can be mainly divided into the following most commonly used categories.

    • String functions - work with string data types
  • Numeric functions - work with numeric data types
  • Date functions - work with date data types
  • Aggregate functions - operate on all of the above data types and produce final result sets.
  • Other Functions − MySQL also supports other types of built-in functions, but we will limit our tutorial to the above functions only.


Let's now look at each of the features mentioned above in detail. We will explain the most commonly used functions using our "Myflixdb".

String Functions

We have already looked at what string functions do. We'll look at a practical example that uses them. Our movie table stores movie titles using combinations of lowercase and uppercase letters. Let's say we want to get a list of queries that returns movie titles in capital letters. We can use the "UCASE" function to do this. It takes a string as a parameter and converts all letters to upper case. The script shown below demonstrates the use of the "UCASE" function.

SELECT `movie_id`,`title`, UCASE(`title`) FROM `movies`;


  • UCASE(`title`) is a built-in function that takes a title as a parameter and returns it in all caps, aliased as `upper_case_title`.

Running the above script in MySQL Workbench against Myflixdb gives us the following results shown below.

movie_id title UCASE('title')
sixteen 67% Guilty 67% GUILTY
6 Angels and Demons ANGELS AND DEMONS
4 Code Name Black CODE NAME BLACK
5 Daddy's Little Girls DADDY'S LITTLE GIRLS
7 Davinci Code DAVINCI CODE
2 Forgetting Sarah Marshall FORGETTING SARAH MARSHAL
9 honey mooners HONEY MOONERS
nineteen movie 3 MOVIE 3
one Pirates of the Caribbean 4 PIRATES OF THE CARIBEAN 4
eighteen sample movie SAMPLE MOVIE
17 The Great Dictator THE GREAT DICTATOR
3 X-Men X-MEN

MySQL supports a number of string functions.

Numeric functions

As mentioned earlier, these functions operate on numeric data types. We can perform mathematical calculations on numeric data in SQL statements.

Arithmetic operators

MySQL supports the following arithmetic operators that can be used to perform calculations in SQL statements.




Integer division








% or MOD



Let's now look at examples of each of the above operators

Integer division (DIV)


Executing the above script gives us the following results.


Division operator (/)

Let's now look at an example of the division operator. We will change the DIV example.

SELECT 23 / 6 ;

Executing the above script gives us the following results.


Subtraction operator (-)

Let's now look at an example of a subtraction operator. We will use the same values ​​as in the previous two examples.

SELECT 23 - 6 ;

Running the above script gives us 17


Addition operator (+)

Let's now look at an example of the addition operator. We will modify the previous example.

SELECT 23 + 6 ;

Running the above script gives us 29


Multiplication operator (*)

Let's now look at an example of the multiplication operator. We will use the same values ​​as in the previous examples.

SELECT 23 * 6 AS `multiplication_result`;

Executing the above script gives us the following results.




Modulo operator (-)

The modulo operator divides N by M and gives us the remainder. Let's now look at an example of a modulo operator. We will use the same values ​​as in the previous examples.

SELECT 23 % 6 ;




Executing the above script gives us 5


Let's now look at some of the common numeric functions in MySQL.

Floor - This function removes decimal places from the number and rounds to the nearest smallest 

umber. The script below demonstrates its use.

SELECT FLOOR(23 / 6) AS `floor_result`;


Executing the above script gives us the following results.



Round - This function rounds a number with decimal places to the nearest whole number. The script below demonstrates its use.

SELECT ROUND(23 / 6) AS `round_result`;

Executing the above script gives us the following results.




Rand - This function is used to generate a random number whose value changes every time the function is called. The script below demonstrates its use.

SELECT RAND() AS `random_result`;


Stored Functions

Stored functions are similar to built-in functions, except that you must define the stored function yourself. Once a stored function has been created, it can be used in SQL statements like any other function. The basic syntax to create a stored function is as below

CREATE FUNCTION sf_name([parameter(s)])
   RETURNS data type



  • "CREATE FUNCTION sf_name ([parameter(s)])" is required and tells the MySQL server to create a function named "sf_name" with optional parameters defined in parentheses.
  • "RETURNS data type" is required and specifies the type of data the function should return.
  • "DETERMINISTIC" means that the function will return the same values ​​if given the same arguments.
  • "STATES" is the procedural code that executes the function.


Let's now look at a practical example that implements a built-in function. Suppose we want to know which rented movies are overdue. We can create a stored function that takes a return date as a parameter and then compares it to the current date on the MySQL server. If the current date is less than the movie's return date, we return "No", otherwise we return "Yes". The script shown below helps us achieve this.

CREATE FUNCTION sf_past_movie_return_date(return_date DATE)
     DECLARE sf_value VARCHAR(3);
        IF curdate() > return_date
            THEN SET sf_value = 'Yes';
        ELSEIF curdate() <= return_date
            THEN SET sf_value = 'No';
        END IF;
     RETURN sf_value;
    END |


When the above script was executed, the stored function `sf_past_movie_return_date` was created.

Let's now check our stored function.

SELECT `movie_id`,`membership_number`,`return_date`,CURDATE() ,sf_past_movie_return_date(`return_date`) FROM `movierentals`;



Running the above script in MySQL Workbench against myflixdb gives us the following results.

movie_id membership_number return_date CURDATE() sf_past_movie_return_date('return_date')
one one NULL 04-08-2012 NULL
2 one 25-06-2012 04-08-2012 yes
2 3 25-06-2012 04-08-2012 yes
2 2 25-06-2012 04-08-2012 yes
3 3 NULL 04-08-2012 NULL


Custom Functions

MySQL also supports user-defined functions that extend MySQL. User-defined functions are functions that can be created with a programming language like C, C++, etc. and then added to the MySQL server. Once added, they can be used like any other function.



  • Functions allow us to extend the capabilities of MySQL.
  • Functions always return a value and can optionally take parameters.
  • Built-in functions are functions that come with MySQL. They can be categorized according to the types of data they work with, i.e. strings, date, and numeric built-in functions.
  • Stored functions are created by the user in the MySQL server and can be used in SQL statements.
  • User defined functions are created outside of MySQL and can be included in the MySQL server.