SQL select statements

[apg1985]

Tweet

Hi,

How would you filter rows in a table which contained a word?

Say if I had a column in the db called paint, the rows for this column where say:

cheap red paint
cheap blue paint
very cheap red paint

how would I filter on the word 'red' so it pulled:

cheap red paint
very cheap red paint

Ive tried this -> "SELECT * FROM test WHERE paint LIKE '%red%'"

but this will only grab the records where 'red' is a the beginning of the sentence, I need it to look through the sentence for the word 'red' and pull it out.

Cheers

[michaeldim]

I think something like this may work SELECT LEFT( paint, LOCATE( "red", paint ) ) FROM table;

MySQL locate() function < MySql Tutorial | w3resource

[nick-harper]

That code "should" pick it anything with the word red in?

[Aquanuke]

Use mysql fulltext search

Using MySQL Full-text Searching