How to Implement the SQL INSTR() Function: A Complete Guide

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.

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.

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.

Example:

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.

Example:

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 the first occurrence of the character ‘a’ of each data value present in the column - ‘city’ of table - ‘Info’.

Example:

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:

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).

Example:

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

Output:

0

Conclusion of How to Implement the SQL INSTR() Function

By this, we have come to the end of this topic. The SQL INSTR() function is a versatile tool for locating the first occurrence of characters or strings in data. Its simplicity and utility make it an essential feature in SQL for managing string data efficiently. Please feel free to comment below in case you come across any doubts or require further clarification.