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.
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.
SQL INSTR() function accepts two parameters:
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.
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 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');
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.
If you’ve enjoyed this tutorial and our broader community, consider checking out our DigitalOcean products which can also help you achieve your development goals.