Javatpoint Logo
Javatpoint Logo

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
6

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
7

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

Output:

INSTR_Substring_Position
18

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

SELECT INSTR( '##@@98221545#@#', '@') AS [email protected]_Position;

Output:

[email protected]_Position
3

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

Output:

INSTR_2021_Position
1

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:

The following CREATE statement creates the Teacher_Info table:

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

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:

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:

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

Output:

Teacher_First_Name instr_a_Position Teacher_Last_Name instr_h_Position
Arush 1 Sharma 2
Bulbul 0 Roy 0
Saurabh 2 Roy 0
Shivani 5 Singhania 5
Avinash 1 Sharma 2
Shyam 4 Besas 0






Youtube For Videos Join Our Youtube Channel: Join Now

Feedback


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA