MySQL Functions: Cheatsheet with examples

All commonly used MySQL functions in one place with examples and a short explanation.

Sujan Shirol
Analytics Vidhya
Published in
9 min readSep 23, 2020

--

MySQL functions
Source: cleanpng.com

There are a ton of functions provided by MySQL and most of them are used extensively. I will be providing the most commonly used functions with a short description. The intension of the article is to provide one spot for all MySQL functions so that one can quickly go through it before your interview or an examination. I’m assuming you already have basic knowledge of SQL. Without wasting your time let me directly jump into the functions.

Before that, I would like you to know that I have used MySQL Workbench to execute the queries and employee database. Let’s quickly have a look at the employee dataset description.

MySQL functions
  1. EMPNO: Employee ID
  2. ENAME: Employee name
  3. JOB: Designation
  4. MGR: Manager ID
  5. HIREDATE: Date when the employee was hired
  6. SAL: Salary per month
  7. COMM: Commission earned
  8. DEPTNO: Department number the employee belongs to

Also, upcoming queries would be more clear if you know the values in the employee table.

SELECT * FROM emps;
MySQL functions

LENGTH( )

CHAR_LENGTH, CHARACTER_LENGTH, and LENGTH, all three functions give the same result which is the number of characters in the given string. All three take one parameter which is a string value.

SELECT ename, CHAR_LENGTH(ename), CHARACTER_LENGTH(ename), LENGTH(ename) FROM emps;
MySQL functions

CONCAT( )

The concatenation of string is a very commonly used technique in all programming languages. SQL provides it too. CONCAT is used to join two or more values into a single string value, it can join any type of data.

SELECT CONCAT(ename, ' works as ',job) FROM emps;
MySQL functions

FORMAT( )

It formats the floating-point number to specified decimal places and returns the value as a string.

Parameters

number: Required. The number to be formatted

decimal_places: Required. The number of decimal places for number. If this parameter is 0, this function returns a string with no decimal places

SELECT comm, FORMAT(comm, 1) FROM emps;
MySQL functions

INSERT( )

Used to insert a string in place of another string starting from a specified position until a certain number of characters.

In this example, we are replacing all JOB values to ‘company employee’ starting from position 1 of JOB value taking all the characters of it(length).

Parameters

string: Required. The string that will be modified

position: Required. The position where to insert string2

number: Required. The number of characters to replace

string2: Required. The string to insert into the string

SELECT ename, job, INSERT(JOB,1, length(job), 'company employee') FROM emps;
MySQL functions

INSTR( )

Returns position of the 1st occurrence of the string in another string. Here, ‘g’ 1st occurs at position 19 in the string ‘ Medium is best blogging platform’.

SELECT INSTR('Medium is best blogging platform', "g") AS MatchPosition;OUTPUT: 19

LOCATE( )

Locate is the improved version of INSTR which addresses the drawback of INSTR. What if we want the position of the third occurrence of the string? LOCATE gives us the flexibility to specify from what position to start the search from. Below, we start searching from position 21 of the string ‘Medium is best blogging platform’ to get the position of the third occurrence of ‘g’.

SELECT LOCATE("g", "Medium is best blogging platform", 21) AS MatchPosition;OUTPUT: 23

UCASE( ), LCASE( )

Very straight forward, UCASE to convert string to uppercase and LCASE to convert a string into lowercase.

SELECT job, LCASE(job), UCASE(job) FROM emps;
MySQL functions

LEFT( ), RIGHT( )

Left: Extract the specified number of characters from the beginning of the string.

Right: Extract the specified number of characters from the end of the string.

Below, we are extracting one character from the beginning and end of each string.

SELECT job, LEFT(job, 1), RIGHT(job, 1) FROM emps;
MySQL functions

REPLACE( )

Replaces all the occurrences of the specified string with another specified string. We are replacing all the uppercase ‘A’ with lowercase ‘a’ in each job value. I have replaced a single character with another single character but the same can be done with string. Go ahead and experiment, change ‘man’ with ‘women’.

SELECT job, REPLACE(job, 'A', 'a') from emps;
MySQL functions

SUBSTR( )

To extract a substring from a string, we have to specify starting positions and the number of characters needed from the start point. Here, we are extracting the first three characters of each job value. That is, 3 characters starting from position 1 of the string.

SELECT job, SUBSTR(job, 1, 3) AS Abbrevation FROM emps;
MySQL functions

Aggregate Functions

Aggregate functions provided by MySQL are max, min, avg, and count. I have demonstrated each by finding maximum, minimum, mean/average, and the total count of salaries.

SELECT MAX(sal), MIN(sal), AVG(sal), COUNT(sal), SUM(SAL) FROM emps;
MySQL functions

FLOOR( ), CEIL( )

Irrespective of the decimal value, the floor returns the nearest integer less than or equal to the float number, and ceil returns the nearest integer greater than or equal to the float number.

MySQL functions
Source: mathsisfun.com
SELECT comm, FLOOR(comm), CEIL(COMM) FROM emps;
MySQL functions

POWER( )

Returns the value of the number raised to another specified number. In this case, it returns the square of all the salaries.

SELECT ename, sal, pow(sal,2) FROM emps;
MySQL functions

ROUND( )

Rounds the number to a specified number of decimal places. It takes two parameters, the number to be rounded and the required decimal places.

MySQL functions
Source: mathsisfun.com

Commission is rounded to 1, 2, and 3 decimal places respectively.

SELECT comm, ROUND(comm,1), ROUND(comm,2), ROUND(comm,3) FROM emps;
MySQL functions

TRUNCATE( )

Returns the value truncated to a specified number of decimal values. If the second argument is 0 then the decimal point is removed, if positive then the specified number of values in the decimal part is truncated, if negative then the specified number of values in the integer part is truncated.

MySQL functions
Source: slideshare.net
SELECT comm, TRUNCATE(comm,1), TRUNCATE(comm,-1) FROM emps;
MySQL functions

DIfference between round and truncate:

Round, rounds the value to the nearest integer while truncate just drops the extra value.

ADDDATE( )

Used to add a time/date interval to date and then return the date. The adding unit can be of type day, month, year, quarter, etc. The list is as below.

MySQL functions
SELECT hiredate, ADDDATE(hiredate, INTERVAL 10 DAY), ADDDATE(hiredate, INTERVAL 2 MONTH), ADDDATE(hiredate, INTERVAL 2 YEAR) FROM emps;
MySQL functions

CURDATE( ), CURTIME( ), CURRENT_TIMESTAMP( )

This is very simple, returns the current date, current time, and current date and time together known as timestamp.

SELECT curdate(), CURTIME(), current_timestamp();
MySQL functions

DATEDIFF( )

Suppose if we want to display the number of experiences in years an employee has in the company, we need to subtract the current date with the date of hire. This is where DATEDIFF() comes handy, it returns the number of days between two dates.

SELECT ename, hiredate, DATEDIFF(CURDATE(), hiredate) as 'experience in days' FROM emps;
MySQL functions

to get the difference in years we need to do some math explicitly: divide by 365, and round the resultant value.

SELECT ename, hiredate, ROUND(DATEDIFF(CURDATE(), hiredate)/365) as 'experience in years' FROM emps;
MySQL functions

DAYNAME( ), DAYOFMONTH( ), DAYOFWEEK( ), DAYOFYEAR( )

The DAYNAME function returns the name of the day (Sunday, Monday, Tuesday, etc.) given a date value.

The DAYOFMONTH returns the number of days since the beginning of the year given a date value.

The DAYOFWEEK basically returns an integer representing the day of the week starting from Sunday as 0 given the date value. Look at DAYNAME in the below table, Wednesday is the 4th(DAYOFWEEK) day of the week, Friday is the 6th(DAYOFWEEK) day of the week, and so on.

The DAYOFYEAR returns an integer representing the day count since the beginning of the year(January 1st). Below, 17th December 1980 is the 352nd day of the year 1980 from January 1.

SELECT DAYNAME(hiredate), DAYOFMONTH(hiredate), DAYOFWEEK(hiredate), DAYOFYEAR(hiredate) FROM emps;
MySQL functions

EXTRACT( )

Used to extract the specified part of the given date.

SELECT EXTRACT(MONTH FROM hiredate), EXTRACT(YEAR FROM hiredate), EXTRACT(DAY FROM hiredate) FROM emps;
MySQL functions

We can extract any of the given below part of the information from date.

MySQL functions

QUARTER( )

Returns the quarter of the year in which the given date falls in.

  • January-March falls in the 1st quarter.
  • April-June falls in the 2nd quarter.
  • July-September falls in the 3rd quarter.
  • October-December falls in the 4th quarter.
SELECT hiredate, QUARTER(hiredate) FROM emps;
MySQL functions

IF( )

Returns value if the given condition is true else another value.

IF(condition, value_if_true, value_if_false)

SELECT IF(SAL<1000, "YES", "NO") FROM EMPS;
MySQL functions

CASE( )

Suppose we would like to categorize employees based on their salary. Salary less than 1000 as Underpaid, between 1000 and 3000 as Fairly paid, and more than 3000 as Overpaid. We have to use the nested if function as below.

SELECT ename, sal, IF(sal<1000, “Underpaid”, IF(sal>1000 AND sal>3000,’Fairly paid’,’Overpaid’)) FROM EMPS;

This is fine if there are only a few conditions, what if we have several conditions? then we need to use the CASE function as below.

SELECT ename, sal,
CASE
WHEN sal<1000 THEN 'Underpaid'
WHEN sal>1000 AND sal<3000 THEN 'Fairly paid'
ELSE 'Overpaid'
END AS 'salary status'
FROM emps;
MySQL functions

COALESCE( )

COALESCE takes a list of arguments and returns the first non-null value. In the below example, if the value of comm is null then it returns zero.

SELECT ename, comm, COALESCE(comm, 0) FROM emps;
MySQL functions

DATABASE( )

It returns the name of the current database you are working in.

SELECT DATABASE();

ISNULL( )

Returns 0 if the given value of a non-null else returns 1.

SELECT comm, ISNULL(comm) FROM emps;
MySQL functions

NULLIF( )

It takes two arguments and returns null if both the values are the same else the first argument passed. Arguments can be of any type.

SELECT NULLIF(25, 25);
MySQL functions

Below we are comparing if salary and commission of each employee are the same. We can see no employee has the same salary and commission hence returns salary since it is the first argument passed.

SELECT sal, comm, NULLIF(sal, comm) FROM emps;
MySQL functions

Hope this was helpful and thank you for reading.

Photo by Pete Pedroza on Unsplash

--

--

Analytics Vidhya
Analytics Vidhya

Published in Analytics Vidhya

Analytics Vidhya is a community of Generative AI and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Sujan Shirol
Sujan Shirol

Written by Sujan Shirol

Editor @ Towards AI | Studying Master's in Data Science | www.sujanshirol.com

Responses (3)