SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL String Functions

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
You can read how replace command is used in query
Read how sql LIKE is used to match strings in a field.

Download sql dump of this student table

Google+

Robin10-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
smo12-04-2009
select * from table where min < 700 and max > 1300 , you can also use Between query
Troy08-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.
Hossein30-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 master10-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 bardhan07-04-2010
min max
0 1300

query-select * from table1 where min=0 and max=1300
dont know sql13-12-2012
how can i Find a particular text from all tables in DB.
Prashant Negi22-06-2013
how can i replace MDH DHANIA POWDER with ABC DHANIA POWDER
rohit05-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
Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked




HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer