MySQL SUBSTR() function with examples

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

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

SUBSTR(str, pos, len)

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

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

SUBSTR(str FROM pos FOR len)

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

If FOR len presents, SUBSTR 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 Internet, starting at position 1. It extracts from the first character to the end of the string.

-- Result: Internet
select substr("Internet", 1);

-- Result: Internet
select substr("Internet" from 1);

2. Query below returns a substring from string Internet, starting at position 3. It extracts all the remaining characters starting from the third character which is t.

-- Result: ternet
select substr("Internet", 3);

-- Result: ternet
select substr("Internet" from 3);

3. Query below returns a substring from string Internet, 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 substr("Internet", 0);

-- Result: empty string
select substr("Internet" from 0);

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

-- Result: tern
select substr("Internet", 3, 4);

-- Result: tern
select substr("Internet" from 3 for 4);

5. Query below returns a substring from string Internet, starting at position -2. It returns the last 2 characters et. In this case, a negative value -2 for pos indicates that the SUBSTR() function extracts from the second last characters to the end of the string.

-- Result: et
select substr("Internet", -2);

-- Result: et
select substr("Internet" from -2);

6. Query below returns a substring of 2 characters long from string Internet, starting at position 3 which is obtained by counting from right to left.

-- Result: ne
select substr("Internet", -3, 2);

-- Result: ne
select substr("Internet" from -3 for 2);

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 MID() function with examples

21. MySQL LOCATE() 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