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)
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/MySQL-INSTR-example-1.jpg?resize=201%2C72)
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)
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/MySQL-INSTR-case-sensitive-example.jpg?resize=230%2C83)
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.
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/MySQL-INSTR-example.jpg?resize=201%2C72)
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 MySQL INSTR in WHERE clause](https://i0.wp.com/www.mysqltutorial.org/wp-content/uploads/2013/12/MySQL-INSTR-in-WHERE-clause.jpg?resize=229%2C153&ssl=1)
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)
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/MySQL-INSTR-vs-LIKE.jpg?resize=156%2C64)
You can check it using the EXPLAIN
statement:
EXPLAIN SELECT
productname
FROM
products
WHERE
productname LIKE '1900%';
Code language: SQL (Structured Query Language) (sql)
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/MySQL-INSTR-LIKE-with-INDEX.jpg?resize=738%2C45)
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)
![](https://i0.wp.com/python-tutorials.in/wp-content/uploads/2022/09/MySQL-INSTR-with-INDEX.jpg?resize=705%2C44)
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.