Return the position of the first occurrence of a substring in a string.
MySQL INSTR() Function
Summary: This tutorial shows you how to use the MySQL INSTR() function to return the position of the first occurrence of a string.
Introduction to the MySQL INSTR() function
Sometimes, you want to locate a substring in a string or to check if a substring exists in a string. In this case, you can use a string built-in function called INSTR.
The INSTR function returns the position of the first occurrence of a substring in a string. If the substring is not found in the str, the INSTR function returns zero (0).
The following illustrates the syntax of the INSTR function.
INSTR(str_statement, substr_what_we_want_search);The INSTR function accepts two arguments:
- The
stris the string that you want to search in. - The
substris the substring that you want to search for.
The INSTR function is not case-sensitive. It means that it does not matter if you pass the lowercase, uppercase, title case, etc., the results are always the same.
If you want the INSTR function to perform searches in a case-sensitive manner on a non-binary string, you use the BINARY operator to cast a one the argument of the INSTRfunction from a non-binary string to a binary string.
The MySQL INSTR() function examples
The following statement returns the position of the substring MySQL in the MySQL INSTRstring.
SELECT INSTR('MySQL INSTR', 'MySQL');
The following statement returns the same result because the INSTR function is case-insensitive.
SELECT INSTR('MySQL INSTR', 'mysql');To force INSTR function to search based on case-sensitive fashion, you use the BINARY operator as follows:
SELECT INSTR('MySQL INSTR', BINARY 'mysql');
The result is different because mysql vs. MySQL now with the BINARY operator.
The INSTR function vs. LIKE operator
We will use the products table in the sample database:
Suppose you want to find a product whose name contains the car keyword, you can use the INSTR function as follows:
SELECT
productName
FROM
products
WHERE
INSTR(productname,'Car') > 0;The > 0 condition is used to filter for rows where 'Car' exists in the product name:
- If 'Car' is found anywhere in the
productname, INSTR will return a position like 1, 5, 10, etc. (any number > 0) - If 'Car' is NOT found in the
productname,INSTR returns 0
By using > 0, you're essentially saying: "Show me all products where the word 'Car' appears somewhere in the product name."
Example:
- Product name: "Sports Car" → INSTR returns 8 (position of 'Car') → 8 > 0 = TRUE → included
- Product name: "Bicycle" → INSTR returns 0 (no 'Car' found) → 0 > 0 = FALSE → excluded
This is equivalent to using LIKE '%Car%' but with a different approach. Both will give you the same results for finding products containing 'Car' in their names[1].
1911 Ford Town Car
1999 Indy 500 Monte Carlo SS
18th Century Vintage Horse Carriage
1917 Maxwell Touring Car
1950's Chicago Surface Lines Streetcar
1962 City of Detroit Streetcar Besides the INSTR function, you can use the LIKE operator to match the Car pattern.
SELECT
productname
FROM
products
WHERE
productname LIKE '%Car%';Both queries return the same result. So which one is faster, the INSTR or the LIKEoperator?
The answer is that they are the same. They are both case-sensitive and perform full table scans.
Let’s create an index on the productname column.
CREATE INDEX idx_products_name ON products(productname)If you use the LIKE operator with the prefix search, on this indexed column, the LIKEoperator will perform faster than the INSTR function.
See the following statement.
SELECT
productname
FROM
products
WHERE
productname LIKE '1900%';1900s Vintage Bi-Plane
1900s Vintage Tri-Plane You can check it using the EXPLAIN statement:
EXPLAIN SELECT
productname
FROM
products
WHERE
productname LIKE '1900%';id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-- ----------- -------- ---------- ----- ----------------- ----------------- ------- ------ ---- -------- ------------------------
1 SIMPLE products (null) range idx_products_name idx_products_name 282 (null) 2 100.0 Using where; Using index 
And compare with the following statement that uses the INSTR function.
EXPLAIN SELECT
productname
FROM
products
WHERE
instr(productname,'1900');id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-- ----------- -------- ---------- ----- ------------- ----------------- ------- ------ ---- -------- ------------------------
1 SIMPLE products (null) index (null) idx_products_name 282 (null) 110 100.0 Using where; Using index 
The INSTR function performs a table scan even though the productname column has an index. This is because MySQL cannot make any assumption about the semantics of the INSTR function, whereby MySQL can utilize its understanding of the semantics of the LIKE operator.
The fastest way to test if a substring exists in a string is to use a full-text index. However, it requires configuring and maintaining the index properly.
When to Use > 0 with INSTR
Key Rule: Use > 0 when you want to check if a substring exists anywhere in the string.
When you DO need > 0:
To check if a substring EXISTS anywhere in the string:
WHERE INSTR(productname, 'Car') > 0 -- Find products containing 'Car'When you DON'T need > 0:
To get the actual position number:
SELECT productname, INSTR(productname, 'Car') AS position
FROM products;
-- This shows the position number (or 0 if not found)To check if substring is at the START of the string:
WHERE INSTR(productname, 'Car') = 1 -- Find products starting with 'Car'To check if substring is at a specific position:
WHERE INSTR(productname, 'Car') = 5 -- Find where 'Car' is at position 5Quick Reference:
- Use
> 0when you want to find rows that contain the substring - Use
= 1when you want to find rows that start with the substring - Use
= specific_numberwhen you want the substring at an exact position - Don't use any comparison when you just want to see the position numbers
Summary
- Use the
INSTRfunction to find the position of the first occurrence of a substring in a string.