The MySQL INSTR function is used to find the position of the first occurrence of a substring within a string. It returns the position of the substring if found, and 0 if the substring is not present in the given string. The INSTR function is case-sensitive, meaning it distinguishes between uppercase and lowercase characters.
Syntax
Here’s the basic syntax of the INSTR function:
INSTR(str, substr)
str: This is the main string or column where you want to search for the substring.
substr: This is the substring you want to find within the main string.
The function returns an integer value representing the position of the first occurrence of the substring within the main string. If the substring is not found, it returns 0.
Example
Here’s an example of using the INSTR function:
SELECT INSTR('Hello, World!', 'World') AS position;
In this example, the function searches for the substring ‘World’ within the string ‘Hello, World!’. The result would be the position of the first occurrence of ‘World’, which is 8.
It’s worth noting that the INSTR function can also take an optional third argument, which specifies the starting position for the search. If this argument is provided, the function begins searching for the substring from that position onward. For example:
SELECT INSTR('Hello, World, World!', 'World', 10) AS position;
In this case, the function starts searching for ‘World’ from position 10 in the string. The result would be the position of the second occurrence of ‘World’, which is 13.
Keep in mind that the MySQL INSTR function may not be supported in other database systems, as it is specific to MySQL. In some other databases, you might use functions like CHARINDEX in SQL Server or POSITION in PostgreSQL to achieve similar functionality.