How to Use the MySQL INSTR Function
In this tutorial, we'll learn how to use the MySQL INSTR() function. INSTR() returns the position of the first occurrence of substring in a given string.
MySQL INSTR Syntax
The following is the syntax of the INSTR() function:
INSTR(string, substring)
Remark
- INSTR() returns 0 if substring or string is NULL, and returns 0 if substring is not in string.
- This function is the same as the two-argument form of the LOCATE function, except that the order of the arguments is reversed.
Examples
Example 1
The following statement returns the position of the first occurrence of tutorial:
SELECT INSTR("Our website provides a lot of useful SQL tutorials. In the first tutorial, you will learn how to use SUM function, and the second tutorial, you will learn how to use COUNT function.", "tutorial") AS Position;
Result:
42
The are 3 tutorial words in the specified string above, but the first occurrence is located at the 42nd position.
Example 2
The following statement returns the position of the first occurrence of guide:
SELECT INSTR("Our website provides a lot of useful SQL tutorials. In the first tutorial, you will learn how to use SUM function, and the second tutorial, you will learn how to use COUNT function.", "guide") AS Position;
Result:
0
Since there are no words guide in the specified string above, INSTR returns 0.
Example 3
The following statement returns the position of the first occurrence of NULL:
SELECT INSTR("Our website provides a lot of useful SQL tutorials. In the first tutorial, you will learn how to use SUM function, and the second tutorial, you will learn how to use COUNT function.", NULL) AS Position;
Result:
NULL
This statement returns NULL since the second argument is NULL.
In this tutorial, we've learned how to use the MySQL INSTR() function. INSTR() returns the position of the first occurrence of substring in a given string. It is the same as the two-argument form of the LOCATE() function, except that the order of the arguments is reversed.