...where products_name REGEXP '[^a-zA-Z]red[^a-zA-Z]'

That seems to work the same as '% red %' it doesnt return the record if red apears right at the start or right at the end of the field.

I could add
LIKE %red %' OR text_field LIKE '% red%'

on the end to also return the record if it is at the start or end of the field. but then am i back to the same problem with puntuation . Ie if the field ends in 'red.' the record wouldnt be returned.

Is REGEXP more efficient than using LIKE?