Mysql Select Content Around Keyword

I'm trying to create the search form with keyword highlighting, but obviously I won't be able to highlight the keyword if it doesn't appear in the first few sentences of the result

Solution 1:

This can be improved but it seems to me you're looking for something like this

select*,
casewhen locate('search_test',field) <10thensubstring(field,1,char_length('search_test')+10)
elsesubstring(field from locate('search_test',field) -10forchar_length('search_test') +20)
endas content
fromtablewhere field like'%search_test%'

Solution 2:

Ok - I think I've figured it out - here is what I've come up with:

SELECT
IF (
    LOCATE('keyword', `content`) < 20,
    SUBSTRING(`content`, 1, 200),
    SUBSTRING(`content`, LOCATE('keyword', `content`) - 20, 200)
) AS `content`
FROM `table`
WHERE `content` LIKE '%keyword%'

First identifying the position of the keyword, then if it's index is less than 20 I simply get the content from the beginning, if it's occuring further then 20th index then I grab it's index minux 20 characters and down to 200 characters all together - and it works!

Thanks Nick - your answer was really helpful.

Only one thing left - how can I strip html tags from the content field so that they are removed - perhaps using REGEXP - anyone knows how?