In this tutorial, we will see what is the INSTR function, what are its applications, and how to use it through simple and effective examples.
Also read: MySQL Insert Ignore – A Complete Guide
Introduction to the INSTR Function
The INSTR function accepts two parameters. The first is the main string and the second is the substring.
It is used to return the position of the first occurrence of the substring in the string. If the substring is not present in the string, the function simply returns 0.
The function returns the position of the first occurrence and not the index. So, the position count starts from 1.
Syntax of the INSTR Function
The following is the correct syntax of the INSTR function.
INSTR(str,substr);Code language: SQL (Structured Query Language) (sql)The str can be any length of string that you want to search the substring in.
Whereas, the substr can be a character as well as a string that you want to search for in the above string.
Note that, the INSTR function ignores the case of the strings. That means MySQL and mysql are treated equally.
So, even if you search “sql” inside “MySQL”, you will get a valid result.
If you want a strict search in the INSTR function, you can use the BINARY operator. If you are thinking about why the BINARY operator/function is used, you can read our complete detailed guide from here.
MySQL INSTR Examples
Let’s take a simple example first to demonstrate the INSTR function.
SELECT INSTR("MySQLCode","sql");Code language: SQL (Structured Query Language) (sql)Here, we are searching for the string ‘sql’ in the ‘MySQLCode’. Note that, we are searching irrespective of the case of the string.
As you can see, the first occurrence of the string ‘sql’ is 3.
Here is how it works-
The function will check if the substring ‘sql’ is present in the string ‘MySQLCode’. If it is present, then check the position of the first letter of the substring in the main string, i.e., the position of ‘s’. When the function finds the first occurrence of the ‘s’, it will return that position count.
Now let’s force the INSTR function to search based on a case-sensitive fashion.
SELECT INSTR(BINARY "MySQLCode","sql");Code language: SQL (Structured Query Language) (sql)The above query will return 0 as there is no match found.
But, if we make the ‘sql’ to uppercase, it will return a positive result.
SELECT INSTR(BINARY "MySQLCode","SQL");Code language: SQL (Structured Query Language) (sql)
Besides a substring, you can also provide a character as a second argument.
SELECT INSTR("MySQLCode", 'M');
SELECT INSTR("MySQLCode", 'e');Code language: SQL (Structured Query Language) (sql)In the above examples, we will simply find the first occurrence of given characters.
INSTR Function as a LIKE Operator
The INSTR function can be used as a LIKE operator in the query. However, it can not fulfil all the functionalities that the LIKE operator provides us.
Let’s take an example to demonstrate how the INSTR function can be used as a LIKE operator.
We have a table called ’emps’ where data of employees is stored.
Now, we want to display all the names of the employee which have the letter ‘a’ in their name.
SELECT * FROM emps WHERE INSTR(name,'a');Code language: SQL (Structured Query Language) (sql)The above query is equivalent to-
SELECT * FROM emps WHERE name LIKE '%a%';Code language: SQL (Structured Query Language) (sql)In the above query, the INSTR function will check if the letter ‘a’ is present in the name of the employee. If it is present, it will return the position.
Note that, all values other than 0 are treated as true. So, whenever we get any position of the occurrence of the character, the where condition becomes true and we get the result.
As you can see, we get all the names that consist letter ‘a’.
Conclusion
In this tutorial, we have learned what is the INSTR function, what it does and how to use it to find the position of the substring in a particular string. We have also seen how can we use it as a like operator in some cases to find if the given character or a string is present in the data and return them.