MySQL Functions: Cheatsheet with examples

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

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.

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;

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;

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;

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;

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;

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;

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;

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.

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;

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

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:

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

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;

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;

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.

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.

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;

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.

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.

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

Hope this was helpful and thank you for reading.

Photo by Pete Pedroza on Unsplash