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.
| 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.
Read how sql LIKE is used to match strings in a field.
| 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 |
|
If you are facing any problem in viewing this page, please tell us