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);

Try It Out

The INSTR function accepts two arguments:

• The str is the string that you want to search in.
• The substr is 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

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 5

Quick Reference:

• Use > 0 when you want to find rows that contain the substring
• Use = 1 when you want to find rows that start with the substring
• Use = specific_number when 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 INSTR function to find the position of the first occurrence of a substring in a string.