LOCATE Function in SQL

This string function of Structured Query Language returns the index value of the searching string in the original string. If the searched string is not found in the original string, then the function returns 0.

Syntax of LOCATE String Function

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

SELECT LOCATE ( substring , Column_Name, [start_position]) AS Alias_Name FROM Table_Name;

In the syntax, we have to specify the name of that column on which we want to run th LOCATE function. In the function start_position parameter is optional.

Syntax2: This syntax uses the LOCATE function with the original string:

Examples of LOCATE String function

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

Output:

LOCATE_P_Position

Example 2: The following SELECT query shows the position of a substring from the second index value in the original string using the LOCATE string function:

Output:

LOCATE_.com12_Position

Example 3: This example uses the LOCATE function with the SQL table

To understand the LOCATE 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 LOCATE function with the Teacher_Address column of the above Teacher_Info table:

SELECT Teacher_Address, LOCATE('Vihar', Teacher_Address, 3) AS LOCATE_Vihar_Position FROM Teacher_Info;

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

Output:

Teacher_Address	LOCATE_Vihar_Position
Aman Vihar	4
Nirman Vihar	6
Sector 128	0
Vivek Vihar	5
Sarvodya Calony	0
Krishna Nagar	0

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

SELECT Teacher_First_Name, LOCATE('a', Teacher_First_Name, 1) AS LOCATE_a_Position, Teacher_Last_Name, LOCATE('h', Teacher_Last_Name, 1) AS LOCATE_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: