MySQL INSTR()

In SQL language, the INSTR() function is used by MySQL and Oracle to get the position of an occurrence in a string. This function allows knowing if a string is present in another string and to get its position at the same time.

Note: the function is identical to LOCATE() with the only difference that the 2 parameters that compose it are reversed.
 

 

Syntax:

This function is used with the following syntax:

SELECT INSTR(str1 , str2);

In the above query, the INSTR() function will search for and return the position of str2 in str1. If the string str2 is not present in str1, then the function will return the position 0.

It is also important to know that if the string str2 is present several times in str1, then only the position of the first occurrence will be returned.
 

Example 1:

To show exactly how useful this function is, let’s take a simple SQL query in which we are going to search for the word “world” in the string “hello world!

SELECT INSTR('hello world!' , 'world');

Output:

7

The result of the query returns the position of the beginning of the word “world”.
 

 

Example 2:

Let’s suppose a table that contains the different units related to meters (kilometer, millimeter, centimeter …).

+----------+------------+
|    id    |    unit    |
+----------+------------+
|    1     | kilometer  |
|    2     | millimeter |
|    3     | meter      |
|    4     | centimeter |
|    5     | mm         |
+----------+------------+

 
In this tutorial, we will try to get the position of the word “meter” in the “unite” column. For this purpose it is possible to execute the following query:

SELECT `id`, `unit`, INSTR(unit, `metre`) As instr_unit
FROM `metre`

Output:

+----------+------------+-----------------+
|    id    |    unit    |   instr_unit    |
+----------+------------+-----------------+
|    1     | kilometer  | 5               |
|    2     | millimeter | 6               |
|    3     | meter      | 1               |
|    4     | centimeter | 6               |
|    5     | mm         | 0               |
+----------+------------+-----------------+

The results show that it is possible to get the position of the searched word and the function returns zero if the string has not been found.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More