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.
SELECT LOCATE('xy','axyghtxyrt',4)
The output is 7 ( not 2 ) as the we have used 4 as position to start the search.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 |
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 |
SELECT LOCATE( '@', 'myname@example.com' )
Output is 7
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" |