How to implement the SQL INSTR() function?

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

• SQL INSTR() function – Documentation