Extract a search string in context

Question

I'm trying to do a MySQL query where I extract the search string in context. So if the search is "mysql" I'd like to return something like this from the 'body' column

"It only takes minutes from downloading the MySQL Installer to having a ready to use"

This is what I've got now but it doesn't work because it just grabs the first 20 characters from the body field. While I'd like it to grab 20 chars in front of and behind the searched term so that the user can see what there term looks like in context:

SELECT id, title, substring(body, 0, 20)  FROM content WHERE body LIKE '%mysql%' OR title LIKE '%mysql%';

Thanks

Consider using [full-text search](http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html) instead of `LIKE` — Phil, Jun 03 '11 at 00:49

Bohemian · Accepted Answer · 2011-06-03T01:31:59.017

2

Here's the SQL you need:

SELECT
    id,
    title,
    substring(body,  
        case
             when locate('mysql', lower(body)) <= 20 then 1
             else locate('mysql', lower(body)) - 20
        end,
        case
            when locate('mysql', lower(body)) + 20 > length(body) then length(body)
            else locate('mysql', lower(body)) + 20
        end)
FROM content
WHERE lower(body) LIKE '%mysql%'
    OR title LIKE '%mysql%'
limit 8;

FYI: Tested and works.

edited Jun 03 '11 at 01:31

answered Jun 03 '11 at 00:42

Bohemian

412,405
93
575
722

Whoa! Didn't expect all that. I tested this out and it works _mostly_. It doesn't strip the end of the body 20 characters after the search term(mysql). – Pardoner Jun 03 '11 at 04:16
Also... I get this error "ERROR: No query specified" when run from the command line. Any idea why? – Pardoner Jun 03 '11 at 04:25
Note that this only retrieves the context of the first instance of the search term. You might want to combine this with something that returns the number of instances of the search term so that you can rank your results, e.g. the answer to [this question](http://stackoverflow.com/questions/12344795/count-the-number-of-occurences-of-a-string-in-a-varchar-field) – Coder Sep 05 '15 at 11:43

Nick Rolando · Answer 2 · 2011-06-03T00:36:42.463

1

You could just pull the whole body value out and just extract the string in your code. If you're using php, you could do something like this, given you've already queried the body string and stored it in a var $body

$index = strpos($body, $searchStr);
$context = substr($body, $index-20, strlen($searchStr)+40);

edited Jun 03 '11 at 00:36

answered Jun 03 '11 at 00:29

Nick Rolando

25,879
13
79
119

The question does not mention PHP at all – Phil Jun 03 '11 at 00:48
Just sayin'. I've seen answers downvoted to the depths of hell for suggesting languages / libraries not tagged in the question. – Phil Jun 03 '11 at 01:05
@Phil I was merely giving an example to demonstrate what I was talking about ("If you're using php.."). Since no language was mentioned, I gave it with the most common language for mysql. – Nick Rolando Jun 03 '11 at 16:13

Extract a search string in context

2 Answers2

Linked