Updated March 13, 2023
Introduction to SQL INSTR()
INSTR() is a string function in standard query language (SQL) which returns the starting position or location of a substring or pattern in the given input string. The INSTR() function is specific to Oracle/PL and MYSQL. However, other SQL database servers like PostgreSQL, SQL server support string functions to determine the location of a substring, but they differ a little bit in syntax. The function similar to INSTR() in PostgreSQL is SUBSTRING() function while in SQL server we have CHARINDEX() function.
SQL INSTR() function returns the first occurrence of a substring in an input string. It is a case sensitive function in ORACLE/ PL SQL. It is not case sensitive in MYSQL as we will see in the examples below.
Syntax and Parameters of SQL INSTR()
In this section, we will be discussing the syntax of INSTR() function in ORACLE/ PL SQL and similar functions in other SQL databases.
ORACLE/ PL SQL:
INSTR (string, pattern)MYSQL:
INSTR (string, pattern)SQL SERVER:
CHARINDEX(pattern, string, start)All the above-mentioned functions perform the same task. They take a pattern or substring and the input string and return the position of the pattern or substring in the input string.
The parameters used in the above syntaxes as follows:
- String: Mention the input string in which the pattern or substring will be located.
- Pattern: Mention the substring or words whose first occurrence has to be located.
The function in postgreSQL is a bit different as it lets us extract the specified substring or pattern from the given input string based on the starting and ending location. The syntax for the same is as follows:
POSTGRESQL:
SUBSTRING ( string ,start_position, length)Examples:
Here are a few examples to illustrate the syntax and use of the INSTR() function in MYSQL.
In order to do so, let us first create an ‘Actors’ table which contains actor id, his/her name, movie and the city where they live for demonstration purposes. We can use the following SQL statements to perform the task.
CREATE TABLE Actors (
ActorID int,
LastName varchar(255),
FirstName varchar(255),
MovieName varchar(255),
City varchar(255)
);Having created the ‘Actors’ table, let us now feed some information into the table columns using insert statements as shown below.
INSERT INTO Actors
VALUES (11,'Hanks','Tom', 'Sully','Los Angeles'),
(12,'Blunt','Emily','Girl on the train','New York'),
(13,'Hathway','Anne', 'Devil wears Parada','Los Angeles'),
(14,'Palkar','Mithila','Girl in the city','Mumbai'),
(15,'Affleck','Ben','Gone Girl','Los Angeles');select * from Actors;The data in the table after insertion operation looks something like this :
Examples to Implement SQL INSTR()
Now let’s first have a look at how INSTR() function actually works in MYSQL using some very simple examples as follows.
Example #1
Simple SQL queries to demonstrate basic working of INSTR() function.
SELECT INSTR ('FirstTestString','Test')as INSTR_TABLE;Output:
Similarly, let us look at one more example to check if INSTR() is case sensitive or not in MYSQL.
SELECT INSTR('girl on the train','THE') as Position;Output:
It is not case sensitive in MYSQL. But please note that INSTR() function is case sensitive in ORACLE/PL SQL database servers.
In the above example, we can note that blankspaces in between are also counted. Going ahead, we will be practicing more examples based on the ‘Actors’ table which we have just created.
Example #2
Find the place of the first occurence of word ‘Girl’ in the movie names present in the Actors database.
SELECT MovieName, INSTR(MovieName,'Girl') as Position
FROM Actors;Output:
We can observe in the above example that the word ‘girl’ is not present in the movies ‘Sully’ and ‘Devil wears Prada’, hence we got ‘0’ index for them. While for the rest of them we have received the first location/ position of ‘girl’ in the name of the movie.
Example #3
Find the names of movies and the actors who acted in them, where the movie names start with the word ‘Girl’.
This example is primarily to demonstrate the use of INSTR() function in WHERE clause.
SELECT MovieName, FirstName, LastName
FROM Actors
WHERE INSTR(MovieName,'Girl') = 1;Output:
Here in this example, we tried to use the INSTR() function in the WHERE clause part of the SQL query. The function returns the starting location of the word ‘Girl’ in the movie names and then WHERE clause filters the movie names based on the location.
Example #4
Find the names of movies and the actors who acted in them, where the starting location word ‘Girl’ is at the 6th or lesser index.
This example is primarily to demonstrate use of INSTR() function in HAVING clause.
SELECT MovieName, FirstName, LastName
FROM Actors
HAVING INSTR(MovieName,'Girl') < 6;Output:
But here there is on problem, the movie names with no occurrence of word ‘Girl’ also made it to the final results. We can customize it further in the HAVING or WHERE clause part of the query as shown below.
SELECT MovieName, FirstName, LastName
FROM Actors
WHERE INSTR(MovieName,'Girl') != 0
HAVING INSTR(MovieName,'Girl') < 6;Output:
Now we can observe that the irrelevant movies like Sully and Devil wears Prada with no occurrence of the word ‘Girl’ has been removed and only the movie names having starting location of word ‘Girl’ at 6th or lesser index is kept.
Recommended Articles
We hope that this EDUCBA information on “SQL INSTR()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
