SQL LOCATE command to search strings in fields

We can use the LOCATE string command in our SQL to get the position of a string present inside another string. Let us try this command.
select locate('xy',afghytyxyrt)
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.
nameclassmarksex
John DeoFour575male
John Mike Four5 60 male
Alex John Four5 55 male
My John RobFifth5 78male
Big John Four5 55 male
Babby John Four5 69 male
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 )
positionname
1John Deo
0Max Ruin
0Arnold
0Krish Star
1John Mike
4My John Rob

Download sql dump of this student table
Could not connect to MySQL