MySQL INSTR() Function
In this context, we will learn how we can use the MySQL INSTR() function with proper syntax and good examples.
Introduction of MySQL INSTR() function
This function in MySQL returns the location of a substring's first occurrence within a given string.
Sometimes, we want to locate a substring in a string or check if a substring exists in a string. In this case, we can use a built-in string 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 INSTR function is not case-sensitive. It means that it does not matter if we pass the lowercase, uppercase, title case, etc. The results are always same.
Suppose we want the INSTR function to perform searches in a case-sensitive manner on a non-binary string. In that case, we use the BINARY operator to cast one of the arguments of the INSTR function from a non-binary string to a binary string.
Syntax of the MySQL INSTR() function
The syntax of the MySQL INSTR() function is given as follows:
INSTR(string_I, string_II)
Parameters or arguments used in MySQL INSTR() function:
There are two parameters accepted by the INSTR() function in MySQL, which are given as follows:
string_I –
This is the string where the search function occurred.
string_II –
This is the string which will be searched in string I.
Returns:
It will return the position of the first occurrence of a substring within a given string.
Note: The function will return 0 if string_II is not found in string_I. INSTR() function only performs case-insensitive searches.
Application used for INSTR() function:
The INSTR() function can be used in the given below MySQL versions.:
- MySQL 5.7
- MySQL 5.6
- MySQL 5.5
- MySQL 5.1
- MySQL 5.0
- MySQL 4.1
- MySQL 4.0
- MySQL 3.23
Examples of MySQL INSTR() function:
Now, we will look into some MySQL INSTR() function examples and will explore how we can use the INSTR function in MySQL.
Example-1:
Here, we will derive the position of a sub-string.
SELECT INSTR("Python is a powerful Language", "powerful")
AS Found;
Output :
Found
13
1 row in set (0.00 sec)
Example-2:
This example shows how the INSTR() function is case-insensitive.
SELECT
INSTR("Python is a powerful Language", "IS")
AS 'Found1';
INSTR("Python is a powerful Language", "is")
AS 'Found2';
Output:
Found1 Found2
8 8
1 row in set (0.00 sec)
Example-3:
If string_2 is not found in string_1.
SELECT INSTR("Python is awesome", "hey")
AS Found;
Output:
Found
0
1 row in set (0.00 sec)
Example-4:
All possible errors in INSTR() function.
1) If only one parameter is passed.
SELECT INSTR("Python is a powerful Language")
AS 'Found';
Output:
Incorrect parameter count in the call to native function 'INSTR.'
2) If three or more parameters are passed.
SELECT INSTR("Python is a powerful Language", "is", "a", "lang)
AS 'Found';
Output:
Incorrect parameter count in the call to native function 'INSTR.'
Some more MySQL INSTR function examples
The statement below returns the substring MySQL's position in the MySQL INSTR string.
SELECT INSTR('MySQL INSTR', 'MySQL');
Output:
| INSTR('MySQL INSTR', 'MySQL') |
| 1 |
The statement given below returns the same result because the INSTR function is case-insensitive.
SELECT INSTR('MySQL INSTR', 'mysql');
To force the INSTR function to search based on case-sensitive fashion, we use the BINARY operator as follows:
SELECT INSTR('MySQL INSTR', BINARY 'MySQL');
Output:
| INSTR('MySQL INSTR', BINARY 'mysql') |
| 0 |
The result is different because MySQL vs MySQL now with the BINARY operator.
The INSTR function vs LIKE operator
We will use the commodity table in the sample database.
Commodity table
Let's consider we want to find a commodity whose name contains the car keyword. For this situation we are going to use the INSTR function as given below:
SELECT
commodity
FROM
commodity
WHERE
INSTR(commodity,'Car') > 0;
Output:
| Commodity |
| 1934 ford car |
| 1978 indy 500 monte |
| 18th century vintage horse |
| 1917 maxwell touring car |
| 1962 city Detroit streetcar |
Besides the INSTR function, we can also use the LIKE operator to match the Car pattern.
SELECT
commodity
FROM
commodity
WHERE
commodity LIKE '%Car%';
Both queries return the same result. So which one is faster, the INSTR or the LIKE operator?
Answering this question, we can say that they are the same. They are both case-sensitive and perform full table scans.
Let's create an index on the commodity column.
CREATE INDEX idx_commodity_name ON commodity(commodity);
If we use the LIKE operator with the prefix search on this indexed column, the LIKE operator will perform faster than the INSTR function.
Let's visualize the statement given below.
SELECT
commodity
FROM
commodity
WHERE
commodity LIKE '1900%';
We can check it using the EXPLAIN statement, which is given below:
EXPLAIN SELECT
commodity
FROM
commodity
WHERE
commodity LIKE '1900%';
And compare with the following statement that uses the INSTR function.
EXPLAIN SELECT
commodity
FROM
commodity
WHERE
instr(commodity,'1900');
The INSTR function performs a table scan even though the commodity 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.
Application of MySQL INSTR() function:
This function is used to find the position of the first occurrence of a substring in a string.
Summary:
In the above context, we have learned how we can use the INSTR() function in MySQL used to find the position of the first occurrence of a substring in a string.