INSTR Function in SQL

This string function of Structured Query Language shows the first position of the specified substring or pattern in the original string.

Syntax of INSTR String Function

Syntax1: This syntax uses the INSTR function with the column name of the SQL table:

In this syntax, we have to specify the name of that column on which we want to perform the INSTR function.

Syntax2: This syntax uses the INSTR function with the set of characters (string):

Syntax3: This syntax uses the INSTR function with the individual character:

In this syntax, we have to specify that single character whose position we want to find in the string.

Examples of INSTR String function

Example 1: The following SELECT query finds the position of the 'P' character in the JAVATPOINT string:

Output:

INSTR_P_Position

Example 2: The following SELECT query shows the position of a substring in the original string using the INSTR string function:

Output:

INSTR_.com12_Position

Example 3: The following SELECT query shows the position of the CAPITAL OF INDIA string in the original string:

SELECT INSTR( 'NEW DELHI IS THE CAPITAL OF INDIA', 'CAPITAL OF INDIA') AS INSTR_Substring_Position;

Output:

INSTR_Substring_Position

Example 4: The following SELECT query trims the given symbol from the specified string:

SELECT INSTR( '##@@98221545#@#', '@') AS INSTR_@_Position;

Output:

INSTR_@_Position

Example 5: The following SELECT query trims the given set of numbers from the specified string:

Output:

INSTR_2021_Position

Example 6: This example uses the INSTR function with the SQL table

To understand the INSTR function with table, we have to create the SQL table first using CREATE TABLE statement.

The syntax for creating the new table in the SQL database is as follows:

CREATE TABLE table_name
(
1st_Column Data Type (character_size of 1st Column),  
2nd_Column Data Type (character_size of the 2nd column ),  
3rd_Column Data Type (character_size of the 3rd column),  
...  
Nth_Column Data Type (character_size of the Nth column)
);  

The following CREATE statement creates the Teacher_Info table:

CREATE TABLE Teacher_Info
(
Teacher_ID INT NOT NULL PRIMARY KEY,  
Teacher_First_Name VARCHAR (100),  
Teacher_Last_Name VARCHAR (100),  
Teacher_Dept_Id INT NOT NULL,
Teacher_Address Varchar(120),
Teacher_City Varchar (80),
Teacher_Salary INT 
);

The below INSERT queries insert the records of college teachers in the Teacher_Info table:

INSERT INTO Teacher_Info (Teacher_ID, Teacher_First_Name, Teacher_Last_Name Teacher_Dept_Id, Teacher_Address, Teacher_City, Teacher_Salary) VALUES (1001, Arush, Sharma, 4001, Aman Vihar, Delhi, 20000);
INSERT INTO Teacher_Info (Teacher_ID, Teacher_First_Name, Teacher_Last_Name Teacher_Dept_Id, Teacher_Address, Teacher_City, Teacher_Salary) VALUES (1002, Bulbul, Roy, 4002, Nirman Vihar, Delhi, 38000 );
INSERT INTO Teacher_Info (Teacher_ID, Teacher_First_Name, Teacher_Last_Name Teacher_Dept_Id, Teacher_Address, Teacher_City, Teacher_Salary) VALUES (1004, Saurabh, Sharma, 4001, Sector 128, Mumbai, 45000);
INSERT INTO Teacher_Info (Teacher_ID, Teacher_First_Name, Teacher_Last_Name Teacher_Dept_Id, Teacher_Address, Teacher_City, Teacher_Salary) VALUES (1005, Shivani, Singhania, 4001, Vivek Vihar, Kolkata, 42000);
INSERT INTO Teacher_Info (Teacher_ID, Teacher_First_Name, Teacher_Last_Name Teacher_Dept_Id, Teacher_Address, Teacher_City, Teacher_Salary) VALUES (1006, Avinash, Sharma, 4002, Sarvodya Calony, Delhi, 28000);
INSERT INTO Teacher_Info (Teacher_ID, Teacher_First_Name, Teacher_Last_Name Teacher_Dept_Id, Teacher_Address, Teacher_City, Teacher_Salary)VALUES (1007, Shyam, Besas, 4003, Krishna Nagar, Lucknow, 35000);

The following SELECT statement displays the inserted records of the above Teacher_Info table:

Teacher_Id	Teacher_First_Name	Teacher_Last_Name	Teacher_Dept_Id	Teacher_Address	Teacher_City	Teacher_Salary
1001	Arush	Sharma	4001	Aman Vihar	Delhi	20000
1002	Bulbul	Roy	4002	Nirman Vihar	Delhi	38000
1004	Saurabh	Roy	4001	Sector 128	Mumbai	45000
1005	Shivani	Singhania	4001	Vivek Vihar	Kolkata	42000
1006	Avinash	Sharma	4002	Sarvodya Calony	Delhi	28000
1007	Shyam	Besas	4003	Krishna Nagar	Lucknow	35000

Query 1: The following SELECT query uses the INSTR function with the Teacher_Address column of the above Teacher_Info table:

SELECT Teacher_Address, INSTR(Teacher_Address, 'Vihar') AS INSTR_Vihar_Position FROM Teacher_Info;

This SQL SELECT statement shows the position of 'Vihar' string in the address of each teacher.

Output:

Teacher_Address	INSTR_Vihar_Position
Aman Vihar	6
Nirman Vihar	8
Sector 128	0
Vivek Vihar	7
Sarvodya Calony	0
Krishna Nagar	0

Query 2: The following SELECT query uses the INSTR function with the Teacher_First_Name and Teacher_Last_Name columns of the Teacher_Info table:

SELECT Teacher_First_Name, INSTR(Teacher_First_Name, a) AS instr_a_Position, Teacher_Last_Name, INSTR(Teacher_Last_Name, h) AS instr_h_Position, FROM Teacher_Info;

This SQL statement shows the position of character a in the first name and character h in the last name of each teacher.

Output: