How to implement the SQL INSTR() function?

Published on August 3, 2022

Safa Mulani

How to implement the SQL INSTR() function?

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

Hey, folks! In this article, we will be understanding SQL INSTR() function in detail.

Working of SQL INSTR()

SQL, being a query language, contains various in-built functions to deal with the String data values of the database.

One such interesting function is SQL INSTR() function.

SQL INSTR() function detects the first occurrence of a string or a character in the other string. Thus, the INSTR() function witnesses the position of the initial/first occurrence of any string/sub-string in another string data value.

Having understood the working of SQL INSTR() function, let us now go ahead with the structure and syntax of the same.

Understanding the Syntax of INSTR()

SQL INSTR() function accepts two parameters:

  • String/character to search for in the other String data value.
  • The string within which the occurrence of the character/string would be searched for.
INSTR(string1, string2);

The INSTR() function returns an integer value stating the index of the first occurrence of the string to be searched.

Now let us understand the implementation of the INSTR() function in the next section through various examples.

Implementing SQL INSTR() through examples

In the below example, the SQL INSTR() function searches for the first occurrence of the character ‘P’ within the input string data value.

SELECT INSTR('JYPython', 'P');



Apart from searching for the first occurrence of characters within a string, INSTR() function works with string values as well.

Here, we have searched for the first occurrence of the string ‘JournalDev’ within the input data value and returns the position value of it.

SELECT INSTR('Python@JournalDev', 'JournalDev');



In this example, we have created a table with different columns. We have tried to display the index of first occurrence of the character ‘a’ of each data value present in the column - ‘city’ of table - ‘Info’.

create table Info(id integer, Cost integer, city varchar(200));
insert into Info(id, Cost,city) values(1, 100,"Puna");
insert into Info(id, Cost,city) values(2, 50, "Satara");
insert into Info(id, Cost,city) values(3, 65,"Puna");
insert into Info(id, Cost,city) values(4, 97,"Mumbai");
insert into Info(id, Cost,city) values(5, 12,"USA");
SELECT city, INSTR(city, "a") as 1st_Occurrence_of_a
FROM Info;


city	1st_Occurrence_of_a
Puna	4
Satara	2
Puna	4
Mumbai	5

If the string/character to be searched for its first occurrence is not contained or present in the string, the INSTR() function returns zero (0).

SELECT INSTR('Python', 'xx');




By this, we have come to the end of this topic. Please feel free to comment below in case you come across any doubts.

For more such posts related to SQL, please visit SQL JournalDev.


Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us

About the authors
Default avatar
Safa Mulani


Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Featured on Community

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel