This query will return 8 as the searched string is located at the 8th position within the string. Now let us apply this LOCATE command to search for the presence of the name john in the name field (column ) of our student table.
SELECT * FROM `student` WHERE locate( 'john', name )
The output of this query is here.
My John Rob
As you can see we have collected all the records having 'john' in any place in the name column of the student table.
Now let us apply this Locate query to our student table to search for the location of the string 'john'
SELECT locate('john',name) as position,name FROM `student`
The above command will return all the records of with the position of the searched string, if it is not found then 0 is returned. ( Few sample records are displayed here )