Introduction to MySQL INSTR() Function
INSTR() MySQL function is defined as an SQL utility that provides the position of the first occurrence of a substring in the main string as the result when executed in the MySQL server. Typically, this INSTR() function is responsible to display the place or location of a string that we want to know under a string in the records of a table in the database. For this type of searching process and displaying the result data, the function INSTR()implements a case-insensitive search in the table provided with the SQL query. INSTR() in MySQL is a string built-in function that checks whether a substring exists in a string or not. In case, if the occurrence does not exist then, the function returns a zero value or FALSE.
Syntax:
Let us introduce the basic syntax of INSTR() function in MySQL:
INSTR(string, string1)
- Here, the syntax has two parameters. One is ‘string’ which is a required entity that represents the string to be searched in.
- The other one is ‘string1’ that denotes the string required as a substring to search for in the first parameter ‘string’.
- Suppose if string1 is not found in the string parameter searched for then, the query with INSTR() function may result in zero.
- Note that the first location in the string is 1 and does not execute a case-sensitive search. The function accomplishes the query with case-insensitive search.
How Does INSTR() Function Work in MySQL?
INSTR() function in MySQL works as a SQL Keyword that helps to return the initial position of a string searched in the another string. The INSTR() is used with the SELECT SQL statement to fetch the result in MySQL.
Suppose, we take a simple example of INSTR() with SELECT SQL Keyword.
Query:
SELECT INSTR('Educba.com', 'a')as INSTR_TABLE;
In the above, query we will search for ‘a’ in the string ‘educba.com’and the result will show its position. The output will be 6, as the occurrence of substring ‘a’ is at 6th place firstly in the main string.
Output:
As we know that INSTR() is not case-sensitive, so it will not matter if your substring passes any lowercase, sentence case, title case, uppercase, etc., but the function will return the same result.
Supposing, we need to apply INSTR() function to search in case-sensitive method on a non-binary keyword string then, we can add the BINARY MySQL operatorwith the argument or parameter in the function from a non-binary string argument to a binary one.
Like, if we use the below MySQL query then the result will be same because the function I case-insensitive.
Query:
SELECT INSTR('Educba.com', 'A') AS Position;
Output:
But if we operate with the query as follows:
Query:
SELECT INSTR('Educba.com', BINARY 'A') as Position;
Output:
Then, the result will differ as the INSTR() function with BINARY substring takes case-sensitive property.
Examples to Implement MySQL INSTR() Function
Let us illustrate the characteristics of INSTR() function with the following examples. A table named PersonData is used as a sample one to perform queries.
Creating a Person Data Table
CREATE TABLE PersonData ( PID int PRIMARY KEY, PName varchar(255) NOT NULL, PAddress varchar(255), PSalary int, PProfile varchar(255) );
After that we will insert some values with the SQL statement as follows:
INSERT INTO PersonData (PID, PName, PAddress, PSalary, PProfile)
VALUES ('10', 'Monu Sharma', 'Bareilly', '30000', 'Engineer'),
(11,'Nikhil Pandit','New Delhi',10000,'Teacher'),
(12,'Priyanshu k','Nainital',25000,'Civil'),
(13,'Aastha Gupta','Lamjung Nepal',8000,'Laywer'),
(14,'Anita Shah','Katihar Bihar',5000,'Nurse');
Here, is the list of all PersonData table data with the query:
Query:
select * from PersonData;
Output:
1. INSTR() Function Works as LIKE Operator in MySQL
We have considered two SQL statements below where we are searching the person names which includes the string keyword ‘s’:
Query:
SELECT PName FROM PersonData WHERE INSTR(PName,'s');
OR
SELECT PName FROM PersonData WHERE PName LIKE '%S%';
Output:
Here, on execution both returns the identical result set of columns with PName.Whereas the LIKE operator sounds similar but is faster as compared when we require finding from huge data in a database table. The LIKE searches for a specific type of pattern in a table column and results the strings set where such an arrangement of string or group of string exists.
2. INSTR() Function Along with WHERE Clause
Let us consider the table above PersonData with fields (PID, PName, PAddress, PSalary, PProfile).
Query:
SELECT PProfile, INSTR(PProfile,'ne') FROM PersonData WHERE INSTR(PProfile,'ne') > 0;
Output:
The above INSTR() query returns the list of profiles in the first column of the result set where the substring keyword ‘en’ is found in the names of profiles of the PersonData table. Whereas in the second column you will get the integer value which indicates the first occurrence position of the substring ‘en’ within the profile of person. Also, there is an optional condition applied after the WHERE clause which ensures that the INSTR() returns only those profile names within the names where the position of the substring ‘en’ presence is more than zero in the column.
3. INSTR() with Comparison Operator
We have written an SQL query with INSTR() function and WHERE clause. The below query demonstrates that the column name PAddress is provided as the first parameter of the function and other one parameter with a substring together with the use of comparison operator greater than (>) in MySQL.
Query:
SELECT PAddress, INSTR(PAddress,'l') AS RESULT FROM PersonData WHERE INSTR(PAddress,'l') > 0;
Output:
Also, let us try out with another equal to comparison operator by the below SQL statement:
Query:
SELECT PAddress, INSTR(PAddress,'l') AS RESULT FROM PersonData WHERE INSTR(PAddress,'l') = 0;
Output:
The INSTR() provides the output as 0 positions where it could not find any string occurrence on search in the PAddress column in the PersonData table.
Conclusion
- The INSTR() MySQL function helps to locate a substring in a given string to check if it occurs in the string or not.
- Suppose if we add the second parameter or argument value of the INSTR() function as NULL then, the occurrence of string in the first parameter string will also be NULL.
- This Function is useful in performing full scan of table and finding out the presence of a string keyword from a given string or column of the table.
Recommended Articles
This is a guide to MySQL INSTR(). Here we discuss the Introduction to MySQL INSTR() Function and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –
