Javatpoint Logo
Javatpoint Logo

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:

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:



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:



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:

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

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


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.


Teacher_First_Name LOCATE_a_Position Teacher_Last_Name LOCATE_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


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Trending Technologies

B.Tech / MCA