MySQL INSTR() Function

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

Syntax

INSTR(string1, string2)

Parameters

Definition and Usage

• The INSTR() function returns the position of the first occurrence of string2 within string1. The position is 1-based (i.e., the first character of the string is at position 1).
• If string2 is not found within string1, the function returns 0.
• The search is case-insensitive.

Return Values

• If string2 is found, the function returns the position (starting from 1).
• If string2 is not found, the function returns 0.

Usage Examples

Example 1: Search for "3" in the string "W3Schools.com" and return its position

SELECT INSTR("W3Schools.com", "3") AS MatchPosition;

Output:

Explanation: The function finds "3" at position 2 in the string "W3Schools.com".

Example 2: Search for "COM" in the string "W3Schools.com" and return its position

SELECT INSTR("W3Schools.com", "COM") AS MatchPosition;

Output:

Explanation: The function returns 0 because the search is case-insensitive, and "COM" in uppercase does not match "com" in the string.

Example 3: Search for "a" in the CustomerName column and return the position

SELECT INSTR(CustomerName, "a")
FROM Customers;

Output:

Explanation: The function searches for the letter "a" in each value of the CustomerName column and returns the position of the first occurrence.

Technical Details

• Works in: From MySQL 4.0.
• Return Type: Integer (position of the substring).
• Case-Insensitive: The search ignores case differences, so "a" will match "A", for example.

Key Notes

• The search in the INSTR() function starts from the beginning of the string and goes towards the end, returning the position of the first match.
• If the substring is not found, 0 is returned.