MySQL MID() function with examples

MID() function returns a substring from a string starting from the specified position. SUBSTRING() function and SUBSTR() function are synonyms of MID() function.

There are two main forms of using MID() function.

MID(str, pos, len)

In this form, len is an optional argument. If it's omitted, MID() returns a substring starting at position pos and returns everything to the end of the string.

If len presents, MID() returns a substring of len characters long from string str, starting at position pos.

MID(str FROM pos FOR len)

In this form, FOR len is an optional argument. If it's omitted, MID() function returns a substring from string str starting at position pos.

If FOR len presents, SUBSTRING returns a substring of len characters long from string str, starting at position pos.

Parameter Values

Parameter	Description
str	Required. The string to extract substring from.
pos	Required. The string that's to be searched within.
len	Optional. The length of the string to extract. If omitted, the default value is 1. A value of 0 for pos returns an empty string.

Examples:

1. Query below returns a substring from string GeeksEngine, starting at position 1. It extracts from the first character to the end of the string.

-- Result: GeeksEngine
select mid("GeeksEngine", 1);

-- Result: GeeksEngine
select mid("GeeksEngine" from 1);

2. Query below returns a substring from string GeeksEngine, starting at position 6. It extracts all the remaining characters starting from the third character which is E.

-- Result: Engine
select mid("GeeksEngine", 6);

-- Result: Engine
select mid("GeeksEngine" from 6);

3. Query below returns a substring from string GeeksEngine, starting at position 0. It returns an empty string because the first position in value str always starts with a positive or negative number.

-- Result: empty string
select mid("GeeksEngine", 0);

-- Result: empty string
select mid("GeeksEngine" from 0);

4. Query below returns a substring from string GeeksEngine, starting at position 6 and for 3 characters only.

-- Result: Eng
select mid("GeeksEngine", 6, 3);

-- Result: Eng
select mid("GeeksEngine" from 6 for 3);

5. Query below returns a substring from string GeeksEngine, starting at position -4. It returns the last 4 characters gine. In this case, a negative value for pos is used which indicates that the MID() function starts from the end of the string and counts backwards.

-- Result: gine
select mid("GeeksEngine", -4);

-- Result: gine
select mid("GeeksEngine" from -4);

6. Query below returns a substring of 3 characters long from string GeeksEngine, starting at position 6 which is obtained by counting from right to left because the pos value is negative 6.

-- Result: Eng
select mid("GeeksEngine", -6, 3);

-- Result: Eng
select mid("GeeksEngine" from -6 for 3);

Happy Coding!

Other tutorials in this category

1. Using String Functions, Part 1

2. Using String Functions, Part 2

3. Using String Functions, Part 3

4. Using MySQL Date and Time Functions, Part 1

5. Using MySQL Date and Time Functions, Part 2

6. Using MySQL Date and Time Functions, Part 3

7. Using MySQL Control Flow Functions

8. Using MySQL Conversion Functions

9. Using MySQL Comparison Functions

10. Using Aggregate Functions in MySQL

11. MySQL RANK() function

12. MySQL DENSE_RANK() function

13. MySQL ROW_NUMBER() function

14. Compare RANK(), DENSE_RANK(), and ROW_NUMBER() function

15. Using LIMIT and OFFSET to Page Thru Query Result in MySQL

16. Using LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to Page Thru Query Result in MySQL

17. Use LEAST function to find the SMALLEST value from multiple arguments

18. How to make case-sensitive comparison in MySQL

19. Use GREATEST function to find the LARGEST value from multiple arguments

20. MySQL LOCATE() function with examples

21. MySQL SUBSTR() function with examples

22. MySQL POSITION() function with examples

23. How to use MySQL REPLACE function in SELECT and UPDATE queries

Back to Tutorial Index Page

	Custom Search