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 on the list - so I need to create a sql statement, which will populate number of characters of to the left of the found keyword and the same to the right.

Anyone know开发者_JS百科s what the SQL statement would be for something like this?

Ok - to clarify - imagine the following SQL statement:

SELECT * 
FROM `articles`
WHERE `content` LIKE '%keyword%'

Now - this statement will return a list of results which match the WHERE clause.

What I'm doing next is I'm wrapping all keywords withing the result set with the span to highlight the keyword using the following method (PHP):

public static function highlight($string = null, $keyword = null, $class = 'highlight') {
     return str_ireplace($keyword, "<span class=\"{$class}\">{$keyword}</span>", $string);
}

Now this will work fine, but the each result only displays the beginning X number of characters from each result.

What I'm trying to achieve is to first identify the keyword within the content, then grab some content from the left and right of this keyword, so that keyword is always presented in each of the results.

I hope this makes sense.

---

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

select *,
case
when locate('search_test',field) < 10 then substring(field,1,char_length('search_test')+10)
else substring(field from locate('search_test',field) - 10 for char_length('search_test') + 20)
end as content
from table
where field like '%search_test%'

---

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?