22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
Get unlimited access to the best of Medium for less than $1/week. Become a member
MySQL Functions: Cheatsheet with examples
All commonly used MySQL functions in one place with examples and a short
explanation.
Sujan Shirol · Follow
Published in Analytics Vidhya
9 min read · Sep 22, 2020
Listen Share More
Source: cleanpng.com
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 1/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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.
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;
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 2/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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;
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 3/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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;
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;
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 4/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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;
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 5/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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.
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 6/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
SELECT job, LCASE(job), UCASE(job) FROM emps;
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;
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 7/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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;
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;
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 8/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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;
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.
Open in app
Search
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 9/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
Source: mathsisfun.com
SELECT comm, FLOOR(comm), CEIL(COMM) FROM emps;
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;
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 10/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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.
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;
TRUNCATE( )
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 11/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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.
Source: slideshare.net
SELECT comm, TRUNCATE(comm,1), TRUNCATE(comm,-1) FROM emps;
DIfference between round and truncate:
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 12/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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.
SELECT hiredate, ADDDATE(hiredate, INTERVAL 10 DAY),
ADDDATE(hiredate, INTERVAL 2 MONTH), ADDDATE(hiredate, INTERVAL 2
YEAR) FROM emps;
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();
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 13/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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;
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;
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 14/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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;
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 15/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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;
We can extract any of the given below part of the information from date.
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 16/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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;
IF( )
Returns value if the given condition is true else another value.
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 17/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
IF(condition, value_if_true, value_if_false)
SELECT IF(SAL<1000, "YES", "NO") FROM EMPS;
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;
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 18/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
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;
DATABASE( )
It returns the name of the current database you are working in.
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 19/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
SELECT DATABASE();
ISNULL( )
Returns 0 if the given value of a non-null else returns 1.
SELECT comm, ISNULL(comm) FROM emps;
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);
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.
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 20/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
SELECT sal, comm, NULLIF(sal, comm) FROM emps;
Hope this was helpful and thank you for reading.
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 21/30
22/10/2023, 07:33 MySQL Functions: Cheatsheet with examples | by Sujan Shirol | Analytics Vidhya | Medium
Photo by Pete Pedroza on Unsplash
Sql MySQL Programming Computer Science
Follow
Written by Sujan Shirol
160 Followers · Writer for Analytics Vidhya
Editor @ Towards AI | Studying Master's in Data Science | www.sujanshirol.com
https://medium.com/analytics-vidhya/mysql-functions-cheatsheet-with-examples-3a08bb36d074 22/30
