EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL INSTR()
 

MySQL INSTR()

Aanchal Sharma
Article byAanchal Sharma
EDUCBA
Reviewed byRavi Rathore

Updated May 12, 2023

MySQL-INSTR()

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

MySQL INSTR() Example 1

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:

MySQL INSTR() Example 2

But if we operate with the query as follows:

Query:

SELECT INSTR('Educba.com', BINARY 'A') as Position;

Output:

MySQL INSTR() Example 3

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:

MySQL INSTR() Example 4

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:

LIKE Operator Example 1

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:

WHERE Clause Example 2

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:

Comparison Operator Example 3

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:

MySQL INSTR() Example 3

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.

  1. Introduction to MySQL Operators
  2. Top Differences – Jira vs Github
  3. Top 23 MySQL String functions
  4. MySQL vs SQLite | Top 14 Comparisons

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW