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')
This query will return 8 as the searched string is located at the 8th position within the string.
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.
name
class
mark
sex
John Deo
Four5
75
male
John Mike
Four5
60
male
Alex John
Four5
55
male
My John Rob
Fifth5
78
male
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 )
position
name
1
John Deo
0
Max Ruin
0
Arnold
0
Krish Star
1
John Mike
4
My 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.
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"
SQL Video Tutorials