finding the keyword in multiple columns and show the text around it

Question

If I want to do a search on multiple columns for the keyword abc using like %abc%, how would I show some of the text around that keyword. Similar to what Google does with its search results.

Answer 1

What you're looking to do is generally referred to as Full Text Search. You will most likely need to use a separate indexing service like Lucene. You can also look at the other questions on SO tagged full text search.

Answer 2

There is the LOCATE() function and LIKE/REGEX operators you can use, when a Full Text Search index is not possible.

SELECT * FROM table
 WHERE LOCATE("abc",fieldA) 
    OR fieldB LIKE "%abc%"
    OR fieldC REGEX ".*[Aa][bB][Cc].*"

By having your data on different columns you may partially defeat the index on the table when using 'OR' logic.

-- J Jorgenson --