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,substr);Code language: SQL (Structured Query Language) (sql)

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 INSTR function 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 INSTR string.

SELECT INSTR('MySQL INSTR', 'MySQL');Code language: SQL (Structured Query Language) (sql)

Try It Out

The following statement returns the same result because the INSTR function is case-insensitive.

SELECT INSTR('MySQL INSTR', 'mysql');Code language: SQL (Structured Query Language) (sql)

Try It Out

To force INSTR function to search based on case-sensitive fashion, you use the BINARY operator as follows:

SELECT INSTR('MySQL INSTR', BINARY 'mysql');Code language: SQL (Structured Query Language) (sql)

Try It Out

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;Code language: SQL (Structured Query Language) (sql)

Try It Out

Besides the INSTR function, you can use the LIKE operator to match the Car pattern.

SELECT 
    productname
FROM
    products
WHERE
    productname LIKE '%Car%';Code language: SQL (Structured Query Language) (sql)

Try It Out

Both queries return the same result. So which one is faster, the INSTR or the LIKE operator?

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);Code language: SQL (Structured Query Language) (sql)

Try It Out

If you use the LIKE operator with the prefix search, on this indexed column, the LIKE operator will perform faster than the INSTR function.

See the following statement.

SELECT 
    productname
FROM
    products
WHERE
    productname LIKE '1900%';Code language: SQL (Structured Query Language) (sql)

Try It Out

You can check it using the EXPLAIN statement:

EXPLAIN SELECT 
    productname
FROM
    products
WHERE
    productname LIKE '1900%';Code language: SQL (Structured Query Language) (sql)

Try It Out

And compare with the following statement that uses the INSTR function.

EXPLAIN SELECT 
    productname
FROM
    products
WHERE
    instr(productname,'1900');Code language: SQL (Structured Query Language) (sql)

Try It Out

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.

Summary

• Ue the INSTR function to find the position of the first occurrence of a substring in a string.