38 MySQL Functions: Definitions & Examples

Download Now: Free WordPress Website Guide + Checklist
Clint Fontanella
Clint Fontanella

Updated:

Published:

MySQL functions are powerful tools that help data managers perform complex calculations and data manipulation with ease. They can edit rows and tables, alter strings, and help you manage organized and easy-to-navigate databases.

Developer using MySQL functions

In this post, we'll explore the most commonly used MySQL functions with definitions and practical examples to help you better understand and use them in your projects. Get Started with HubSpot's Analytics Software for Free

Table of Contents

MySQL Server Functions

1. If Function: IF()

The if function returns one value if a condition is true and another value if the condition is false.

If Function Example:

SELECT IF(10 > 5, 'Greater than', 'Less than or equal to');

Output:

 'Greater than'

2. Database Function: DATABASE()

The database function returns the name of the current database.

Database Function Example

SELECTDatabase();

Output:

DatabaseName baseball_team

3. Binary Function: BIN()

The binary function converts a decimal number to a binary number.

Binary Function Example

SELECTBin(10);

Output:

1010

4. User Function: USER()

The user function returns the current user name and host name.

User Function Example

SELECT USER(); 

Output:

root@localhost

5. Convert Function: CONVERT()

This function converts a value from one data type to another.

Conver Function Example

SELECT CONVERT('5', UNSIGNED); 

Output:

5

6. Version Function: VERSION()

The version function returns the version of the MySQL server.

Version Function Example

SELECT Version(); 

Output:

8.0.23-0ubuntu0.20.04.1

7. Lead Function: LEAD()

The lead function returns the value of a row further down the result set.

Lead Function Example

SELECT NAME,
       Lead(NAME)
         OVER (
           ORDER BY id) AS next_name
FROM   players; 

Output:

name       | next_name
-----------|-----------
John Smith | Jane Doe
Jane Doe   | Michael Johnson
Michael... | Sarah Thompson
Sarah...   | Tim Lee
Tim Lee    | NULL

8. Sleep Function: SLEEP()

The sleep function delays execution for a specified number of seconds.

Sleep Function Example

SELECTSleep(5);

Output:

Empty set (0.00 sec)

Note: Some of these functions can be used with both SQL and MySQL. You can find more SQL functions in our ultimate guide here.

MySQL String Functions

9. Match Function: MATCH()

The match function searches a string for a pattern and returns the position of the match.

Match Function Example

SELECTMatch('Hello World','World');

Output:

7

10. Lower Function: LOWER()

The lower function returns a string with all characters converted to lowercase.

Lower Function Example

SELECT Lower('Hello World'); 

Output:

'hello world'

11. Upper Function: UPPER()

The upper function returns a string with all characters converted to uppercase.

Upper Function Example

SELECTUpper('Hello World');

Output:

'HELLO WORLD'

12. Left Function: LEFT()

The left function returns a specified number of characters from the beginning of a string.

Left Function Example

SELECT LEFT('Hello World', 5); 

Output:

Hello'

13. Right Function: RIGHT()

The right function extracts a specified number of characters from the right side of a string.

Right Function Example:

SELECTRIGHT('apple',3);

Output:

 'ple'

14. Space Function: SPACE()

The space function returns a string containing a specified number of space characters.

Space Function Example

SELECT Space(5); 

Output:

'     '

15. Locate Function: LOCATE()

The locate function returns the position of the first occurrence of a substring in a string.

Locate Function Example

SELECTLocate('o','Hello World');

Output:

5

16. Insert Function: INSERT()

This function inserts a string within another string at the specified position.

Insert Function Example

SELECT INSERT('Hello World', 6, 0, 'My '); 

Output:

'Hello My World'

17. Field Function: FIELD()

The field function returns the index position of a string within a list of strings.

Field Function Example

SELECT Field('World', 'Hello,World,SQL'); 

Output:

2

18. Repeat Function: REPEAT()

The repeat function eturns a string consisting of a specified number of repetitions of another string.

Repeat Function Example

SELECT Repeat('', 3); 

Output:

**'

MySQL Date Functions

19. Time Stamp Function: TIMESTAMP()

The time stamp function returns the current date and time.

Time Stamp Function Example:

SELECT TIMESTAMP();

Output:

 '2023-05-05 12:00:00'

20. Add Date Function: ADDDATE()

The add date function adds a specified number of days to a date.

Add Date Function Example

SELECTAdddate('2022-05-09',INTERVAL5day);

Output:

2022-05-14

21. Current Date: CURDATE()

The current date function returns the current date of the year. 

Current Date Function Example

SELECT Curdate(); 

Output:

2023-05-9

22. Current Time: CURTIME()

The current time function returns the current time of the day.

Current Time Function

SELECT Curtime() 

Output:

22:30:45

23. Date Function: DATE()

The date function returns the date part from a datetime value.

Date Function Example

SELECT Date('2023-05-09 22:30:45') 

Output:

2023-05-09

24. Day Function: DAY()

The day function returns the day of the month from a date value.

Day Function Example

SELECT Day('2023-05-09') 

Output:

9

25. Hour Function: HOUR()

The hour function extracts the hour from a time value.

Hour Function Example

SELECT Hour('22:30:45'); 

Output:

22

26. Week Function: WEEK()

The week function returns the week number for a date value.

Week Function Example

SELECT Week('2023-05-09') 

Output:

19

27. Year Function: YEAR()

The year function returns the year from a date value.

Year Function Example

SELECT Year('2023-05-09') 

Output:

2023

28. Time Function: TIME()

The time function returns the time part from a datetime value.

Time Function Example

SELECT Time('2023-05-09 22:30:45') 

Output:

22:30:45

MySQL Numeric Functions

29. Round Function: ROUND()

The round function rounds a number to a specified number of decimal places.

Round Function Example:

SELECTRound(3.14159,2);

Output: 

3.14

30. Min Function: MIN()

The min function returns the minimum value from a set of values.

Min Function Example:

SELECTMin(5,10,15,20);

Output: 

5

31. Floor Function: FLOOR()

The floor function rounds a number down to the nearest integer.

Floor Function Example:

SELECTFloor(3.8);

Output: 

3

32. Mod Function: MOD()

The modulo or mod function in MySQL provides the remainder of a number divided by another number.

Mod Function Example:

SELECTMod(7,3);

Output:

1

/*The output would be 2 because 7 divided by 3 creates a remainder of 1.

33. Ceil Function: CEIL()

The ceil function in MySQL rounds a given number up to the nearest integer.

Ceil Function Example:

SELECTCeil(44.6);

Output:

45

/* This example rounds the number 44.6 to the nearest integer, which would be 45.

34. Absolute Value: ABS()

The absolute value function returns the absolute value of a number.

Absolute Value Function Example

SELECTAbs(-10)

Output:

10

35. Exponent Function: EXP()

This function returns the value of the constant e raised to the power of a specified number.

Exponent Function Example

SELECTExp(2)

Output:

7.3890560989306495

36. Pi Function: PI()

The pi function returns the value of pi to 16 decimal places

Pi Function Example

SELECTPi()

Output:

3.141592653589793

37. Logarithm Function: LN()

This function returns the natural logarithm of a number.

Logarithm Function Example

SELECTLn(2)

Output:

0.6931471805599453

38. Count Function: COUNT()

The count function returns the number of rows that match a specified condition.

Count Function Example

SELECTCount(*)
FROMstudents
WHEREage>18

Output:

5

Note: Some of these functions overlap with aggregate functions. To learn more about aggregate SQL functions, check out our guide here

Using MySQL Functions

MySQL functions greatly simplify and streamline your database operations. By leveraging the power of these pre-built functions, you can efficiently perform data calculations, manipulations, and retrievals, ultimately leading to better query performance and more accurate insights.

Author's Note: This post was written/edited by a human with the assistance of generative AI.

analytics

Related Articles

Launch your WordPress website with the help of this free guide and checklist.

    CMS Hub is flexible for marketers, powerful for developers, and gives customers a personalized, secure experience

    START FREE OR GET A DEMO