<p>I'm building a basic search functionality, using LIKE (I'd be using fulltext but can't at the moment) and I'm wondering if MySQL can, on searching for a keyword (e.g. WHERE field LIKE '%word%') return 20 words either side of the keyword, as well?</p>

[{'answer_id': 38063, 'author': 'aryeh', 'author_id': 3288, 'author_profile': 'https://Stackoverflow.com/users/3288', 'pm_score': 0, 'selected': False, 'text': "<p>Use the INSTR() function to find the position of the word in the string, and then use SUBSTRING() function to select a portion of characters before and after the position.</p>\n\n<p>You'd have to look out that your SUBSTRING instruction don't use negative values or you'll get weird results.</p>\n\n<p>Try that, and report back.</p>\n"}, {'answer_id': 38188, 'author': 'JimmyJ', 'author_id': 2083, 'author_profile': 'https://Stackoverflow.com/users/2083', 'pm_score': 0, 'selected': False, 'text': '<p>I don\'t think its possible to limit the number of words returned, however to limit the number of chars returned you could do something like</p>\n\n<pre><code>SELECT SUBSTRING(field_name, LOCATE(\'keyword\', field_name) - chars_before, total_chars) FROM table_name WHERE field_name LIKE "%keyword%"\n</code></pre>\n\n<ul>\n<li>chars_before - is the number of\nchars you wish to select before the\nkeyword(s)</li>\n<li>total_chars - is the\ntotal number of chars you wish to\nselect</li>\n</ul>\n\n<p>i.e. the following example would return 30 chars of data staring from 15 chars before the keyword</p>\n\n<pre><code>SUBSTRING(field_name, LOCATE(\'keyword\', field_name) - 15, 30)\n</code></pre>\n\n<p>Note: as aryeh pointed out, any negative values in SUBSTRING() buggers things up considerably - for example if the keyword is found within the first [chars_before] chars of the field, then the last [chars_before] chars of data in the field are returned.</p>\n'}, {'answer_id': 38245, 'author': 'Fernando Barrocal', 'author_id': 2274, 'author_profile': 'https://Stackoverflow.com/users/2274', 'pm_score': 0, 'selected': False, 'text': '<p>I think your best bet is to get the result via SQL query and apply a regular expression programatically that will allow you to retrieve a group of words before and after the searched word.</p>\n\n<p>I can\'t test it now, but the regular expression should be something like:</p>\n\n<pre><code>.*(\\w+)\\s*WORD\\s*(\\w+).*\n</code></pre>\n\n<p>where you replace <code>WORD</code> for the searched word and use regex group 1 as before-words, and 2 as after-words</p>\n\n<p>I will test it later when I can ask my <a href="http://www.regexbuddy.com/" rel="nofollow noreferrer">RegexBuddy</a> if it will work :) and I will post it here</p>\n'}, {'answer_id': 2509444, 'author': 'Paulo Pinto', 'author_id': 189929, 'author_profile': 'https://Stackoverflow.com/users/189929', 'pm_score': 3, 'selected': True, 'text': "<p>You can do it all in the query using SUBSTRING_INDEX</p>\n\n<pre><code>CONCAT_WS(\n' ',\n-- 20 words before\nTRIM(\n    SUBSTRING_INDEX(\n        SUBSTRING(field, 1, INSTR(field, 'word') - 1 ),\n        ' ',\n        -20\n    )\n),\n-- your word\n'word',\n-- 20 words after\nTRIM(\n    SUBSTRING_INDEX(\n        SUBSTRING(field, INSTR(field, 'word') + LENGTH('word') ),\n        ' ',\n        20\n    )\n)\n</code></pre>\n\n<p>)</p>\n"}]
['https://Stackoverflow.com/questions/38035', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/393028/']