Search String in a Field

[Gian04]

If I have a field named : fruits
with the following record: apple, banana, mango, melon

How can do a query / loop to extract individual fruit?
And a query that will search a fruit, let say I just want to search for mango

Thanks

[Gian04]

Let me clarify my question

apple, banana, mango, melon

is just a single record (meaning 1 record could have many fruit name)

So my question is how can I search for the all records (rows) with

mango

in it?

[deMorte]

I think that one way to accomplish this would be to use LIKE statement:
SELECT * FROM table WHERE fruits LIKE '%mango%'

Of course this doesn't work if you have fruits that have name of another fruit in the name:
SELECT * FROM table WHERE fruits LIKE '%grape%'
This query will find rows with 'grape' and rows with 'grapefruit'.

More info on pattern matching can be found from MySQL Reference Manual:
[dev.mysql.com...]

[Gian04]

Correct. I need a certain function (or what ever) that will find if a certain fruit exist in a row. I need the one that will return true if found an EXACT match.

[deMorte]

I think your best bet would be to go with the REGEXP query.
I'm not very good with regular expressions, but maybe this will work:

function searchFruit($fruit) {

$sql = "SELECT * FROM table WHERE fruits REGEXP '^".$fruit."$'";

// Rest of the code
}

[edited by: deMorte at 11:05 am (utc) on Oct. 14, 2008]

[Anyango]

SELECT * FROM table WHERE fruits LIKE 'mango %' OR fruits like '% mango' or fruits like '% mango %'

notice the use of blank spaces, this will help solve the problem deMorte has mentioned, i.e. it will not return grapefruit for grape.

2) Extract that value in a variable
3) split() the Variable into an array using whatever delimeter you have in your field, i think whitespace?
4) run in_array() on that array and that will return true or false for your fruit name

Infact, i think you only needed that Query, rest is not needed i think but i dont like deleting what i have already posted. That Query itself will return only those rows which have exact match on your fruit name

[edited by: Anyango at 3:43 pm (utc) on Oct. 14, 2008]

[Anyango]

Sorry i didnt notice your delimeter was a comma. So this should work

SELECT * FROM table WHERE fruits LIKE 'mango,%' OR fruits like '%, mango' or fruits like '%, mango,%'

[henry0]

Basic of extraction [webmasterworld.com]

The second question: If you are looking for a precise name then any forms of % will bring the exact match plus variations

if you know what you are looking for you will need a subquery such as

WHERE
fruit='$fruit'

$fruit is the one fruit you are looking for