MySQL INSTR() Function
The INSTR() function in MySQL is used to find the position of a substring within a larger string. It's a very handy tool for working with text data and locating specific parts of strings.
INSTR(): Definition and Usage
INSTR() performs a case-insensitive search. It returns the starting position of the first occurrence of the search string within the main string. If the substring is not found, it returns 0. The position of the first character in a string is 1.
Syntax
Syntax
INSTR(string1, string2)
Parameter Values
| Parameter | Description |
|---|---|
string1 |
The string to search within. This is required. |
string2 |
The substring to search for. This is required. |
Examples
Finding the Position of a Substring
This example searches for "3" within "W3Schools.com" and returns its starting position.
Syntax
SELECT INSTR("W3Schools.com", "3") AS MatchPosition;
Output
3
Finding the Position of Another Substring
Searching for "COM" (case-insensitive match).
Syntax
SELECT INSTR("W3Schools.com", "COM") AS MatchPosition;
Output
10
Finding the Position of a Substring in a Column
This searches for the letter "a" within the 'CustomerName' column of the 'Customers' table. (Assumes a 'Customers' table with a 'CustomerName' column)
Syntax
SELECT INSTR(CustomerName, "a")
FROM Customers;
Output
(The position of the first 'a' in each CustomerName will be returned. 0 if 'a' is not found.)