// Tutorial //

How to implement the SQL INSTR() function?

Published on August 3, 2022
Default avatar
By Safa Mulani
Developer and author at DigitalOcean.
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');

Output:

3

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');

Output:

8

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;

Output:

city	1st_Occurrence_of_a
Puna	4
Satara	2
Puna	4
Mumbai	5
USA	3

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');

Output:

0

Conclusion

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.


References


Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest.

Sign up
About the authors
Default avatar
Developer and author at DigitalOcean.

Still looking for an answer?

Was this helpful?