tag1 - Novel, My pession, Holi, Book, My book, Having book
tag2 - Holi Novel, My pession, Book, My book, Having book
tag3 - Romantic Novel, My Novel, Book, My book, Having book
tag4 - Novel based, My Novel, Book, My book, Having book
tag5 - Ovel based, My Novel, Book, My book, Having book, Novel
tag6 - Based, My Novel, Book, Novel, Having book, Presume
I want to search only those tags having Novel. It should be search tag1 and tag5 in full text search
I usually store each keyword in following manner
,Novel,My Passion,Holi,
i.e. comma at start and at end of string, so now instead of Novel you can search for '%,Novel,%' in LIKE command and it works like a charm.
SELECT * FROM your_table WHERE subject LIKE 'Novel'
UNION
SELECT * FROM your_table WHERE subject LIKE 'Novel,%'
UNION
SELECT * FROM your_table WHERE subject LIKE '%,Novel,%'
UNION
SELECT * FROM your_table WHERE subject LIKE '%,Novel'
WHERE
tag1 REGEXP '(^|, )[[:<:]]Novel[[:>:]](,|$)' AND
tag5 REGEXP '(^|, )[[:<:]]Novel[[:>:]](,|$)'