MySQL INSTR Function

Created with Sketch.

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)

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

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)

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)

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

MySQL INSTR in WHERE clause

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)

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

The answer is the 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)

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)

You can check it using the EXPLAIN statement:

EXPLAIN SELECT
productname
FROM
products
WHERE
productname LIKE '1900%';

Code language: SQL (Structured Query Language) (sql)

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)

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 is required a configure and maintain the index properly.

In this tutorial, you have learned how to use the INSTR function to find the position of the first occurrence of a substring in a string.

Leave a Reply

Your email address will not be published. Required fields are marked *