MySQL POSITION() function with examples

POSITION() function finds the location of the first occurrence of the substring in a given string. It returns an integer number counting from left to right in the given string. If the substring is not found within the given string, POSITION() function returns 0.

LOCATE() function is a synonyms of POSITION() function.

Syntax

POSITION(substr IN str)

Parameter Values

Parameter	Description
substr	Required. The substring to search for within str.
str	Required. The string that's to be searched within.

POSITION() function returns 0 if substr is not in str. Returns NULL if substr or str is NULL.

Examples

1. The query below finds the position of the first occurrence of the dash character "-" within the given string 011-56984589. It returns integer number 4 which counts from left to right in the given string.

-- Result: 4
select position("-" in "011-56984589");

2. The query below shows that there are two dash characters in the given string 011-56984589-22. It returns integer number 4 which is the position of the first occurrence counting from left to right in the given string.

-- Result: 4
select position("-" in "011-56984589-22");

3. The query below finds the position of the first occurrence of the string "012" within the given string 011-56984589. It returns integer number 0 as the substring does not exist in the given string.

-- Result: 0
select position("012" in "011-56984589");

4. In the query below, we want to extract substring "011" which is the string before the first occurrence of the dash character "-". POSITION function here is used to get the position of the first "-" character, then SUBSTR function is used to extract substring "011" from "011-56984589".

-- Result: 011
select substr("011-56984589", 1, position("-" in "011-56984589")-1);

5. In the query below, we want to extract substring "56984589" which is the string after the first occurrence of the dash character "-". POSITION function here is used to get the position of the first "-" character, then SUBSTR function is used to extract substring "56984589" from "011-56984589".

-- Result: 56984589
select substr("011-56984589", position("-" in "011-56984589")+1);

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

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

Back to Tutorial Index Page

	Custom Search