Of course!

I should have thought about putting spaces in

Thanks Dmitriy you were nearly spot on with what you came up with:

SELECT * FROM tbl_name WHERE text_field LIKE '% red %' OR text_field LIKE '%red %' OR text_field LIKE '% red%' ORDER BY field_name;

the only problem came with %red % as this would pick up words ending in 'red' and % red% came up with words begining in red such as redress. However putting spaces in was the clue i was after. The final solution was.

SELECT * FROM tbl_name WHERE text_field LIKE '% red %' OR text_field LIKE 'red %' OR text_field LIKE '% red' ORDER BY field_name;

The only problem with this is punctuation so if somewhere in the description it has "this product is red." then this wouldnt be found. However i can just expand the code with another OR '% red.' OR
'% red. %'

All in all a huge improvement on the old search algorithm which was frankly broken! So thank you