Updated May 12, 2023
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 for displaying 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 built-in string function that checks whether a substring exists in a string. If the occurrence does not exist, the function returns a zero value or FALSE.
Syntax:
Let us introduce the basic syntax of the INSTR() function in MySQL:
INSTR(string, string1)- Here, the syntax has two parameters. One is ‘string’, a required entity representing the string to be searched in.
- The other one is ‘string1′, which denotes the string required as a substring to search for in the first parameter’ string’.
- If string1 is not found in the string parameter searched for, the query with INSTR() function may result in zero.
- Note that the first location in the string is one and does not execute a case-sensitive search. The function accomplishes the query with a 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 search in 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 a case-sensitive method on a non-binary keyword string, we can add the BINARY MySQL operator with the argument or parameter in the function from a non-binary string argument to a binary one.
If we use the below MySQL query, the result will be the 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 the BINARY substring takes case-sensitive property.
Examples to Implement MySQL INSTR() Function
Let us illustrate the characteristics of the 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:
On execution, both return the identical result set of columns with PName. At the same time, the LIKE operator sounds similar but is faster than 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 in the strings set where such an arrangement of strings or group of strings exists.
2. INSTR() Function Along with WHERE Clause
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 INSTR() query returns a list of profiles located in the first column of the result set where the substring keyword ‘en’ appears in the names of profiles stored in the PersonData table. In the second column, you will get the integer value which indicates the first occurrence position of the substring ‘en’ within the person’s profile. Additionally, an optional condition can be applied after the WHERE clause to ensure that the INSTR() query only returns profile names where the position of the substring ‘en’ is greater 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 function’s first parameter and the other one parameter with a substring together with a 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 locate a substring in a given string to check whether it occurs in the string.
- Suppose we add the second parameter or argument value of the INSTR() function as NULL. Then, the occurrence of a string in the first parameter string will also be NULL.
- This Function is useful in performing a full scan of the table and finding out the presence of a string keyword from a given string or column.
Recommended Articles
We hope that this EDUCBA information on “MySQL INSTR()” benefited you. You can view EDUCBA’s recommended articles for more information.
