SQL LOCATE command to search strings and retrun position

We can use the LOCATE string command in our SQL to get the first occurrence ( position )of a string present inside another string. Let us try this command.
SELECT LOCATE('xy','afghytyxyrt'); # Output 8 
This query will return 8 as the searched string is located at the 8th position within the string.
SELECT LOCATE('z','abcdefgh')
Returns 0 if not found.

Position to start searching (Optional)

SELECT LOCATE('xy','axyghtxyrt',4)
The output is 7 ( not 2 ) as the we have used 4 as position to start the search.

By changing the value of 4 to 1 we will get 2 as output.

LOCATE on Name column of Student table

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

Getting position of @ in an email address

SELECT LOCATE(  '@',  'myname@example.com' )
Output is 7

locate and substring

By using the position ( number ) we can take out the rest part of the string by using substring function.

locate and substring_index

By using a delimiter we can take out part of the string from the main string by using substring_index function.

If you have a list of email address in a table then you can find out the position of @ in the address and separate domain and user id part from the address.
FIND_IN_SET: Find a search string within a set of strings
SQL Replace Like command to match string in a field SQL String References
Download sql dump of this student table

Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    Robin

    10-04-2009

    I have a table: columns are as follows. min max 0 1300 1301 2000 2001 3900 now user enter something say (700) i want to use a query such that: it only display: min max 0 1300 Please help me
    smo

    12-04-2009

    select * from table where min < 700 and max > 1300 , you can also use Between query
    Troy

    08-10-2009

    In the same vein as your examples above.. suppose that I have a list of names and I want to return all the records where name1 is found a field, then return all records where name2 is found in a field... and so on. I have a list of 375 names and I'm search a single field for the presence of their name.
    Hossein

    30-01-2010

    I have a table with 2 columns as follows: ------------ id code 1 a 2 b 2 c 1 b 3 c 4 a 1 c 2 a ------------ I want to select id's that has all codes a AND b AND c. Please help for sql command. thanks
    sql master

    10-02-2010

    ooopps, this should be the correct code for the query..

    select * from tablename where
    locate('a', code, 1) <> 0
    or locate('b', code, 1) <> 0
    or locate('c', code, 1) <> 0
    manoj kumar bardhan

    07-04-2010

    min max 0 1300 query-select * from table1 where min=0 and max=1300
    dont know sql

    13-12-2012

    how can i Find a particular text from all tables in DB.
    Prashant Negi

    22-06-2013

    how can i replace MDH DHANIA POWDER with ABC DHANIA POWDER
    rohit

    05-07-2014

    SELECT * FROM `student` WHERE locate( 'john', name )
    -in the above sql query ,only john is given right,
    Suppose a column is as follows
    aaa dd oii qq
    bb ask jiasj sjd
    sxd nj kkk

    and i want to select record having both bb and ask as substring in the column'. what should i do,how is the query for that
    Nishanth

    03-11-2014

    Can you please help me with a query for the below condition:
    To find the position at which the occurance of the string "on" appears the second time in the word "consultation"

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer