MySQL INSTR() Function

 The INSTR() function in MySQL is used to return the position of the first occurrence of a substring in a string. If the substring is not found, the function returns 0.

Syntax

INSTR(string, substring)

• string: The string to search within.
• substring: The substring to find in the string.

Key Points

1. The position is 1-based (the first character has a position of 1).
2. It is case-sensitive.

Example Usage

Example 1: Basic Usage

SELECT INSTR('Hello World', 'World') AS Position;

Output: 7
Explanation: The substring 'World' starts at position 7 in 'Hello World'.

Example 2: Substring Not Found

SELECT INSTR('Hello World', 'MySQL') AS Position;

Output: 0
Explanation: The substring 'MySQL' is not present in 'Hello World'.

Example 3: Case Sensitivity

SELECT INSTR('Hello World', 'world') AS Position;

Output: 0
Explanation: The function is case-sensitive, and 'world' does not match 'World'.

Example 4: Use with Table Data

Assume you have a table products with a column description:

+----+--------------------+
| id | description        |
+----+--------------------+
| 1  | Apple iPhone 13    |
| 2  | Samsung Galaxy S21 |
| 3  | Google Pixel 6     |
+----+--------------------+

You can search for a substring in the description column:

SELECT id, description, INSTR(description, 'Galaxy') AS Position
FROM products;

Output:

+----+--------------------+----------+
| id | description        | Position |
+----+--------------------+----------+
| 1  | Apple iPhone 13    | 0        |
| 2  | Samsung Galaxy S21 | 9        |
| 3  | Google Pixel 6     | 0        |
+----+--------------------+----------+

Additional Notes

• If you need a case-insensitive search, you can use LOWER() or UPPER() in combination with INSTR() or consider using the LOCATE() function.