Find 1st and last 20 character after particular keyword from database?

Question

I want to find first and last 20 character after the particular keyword from database. Suppose the keyword is "Account" then I want to find first 20 character before "Account" appear in a line and 20 character after "Account" appear in a line.

Currently I am using this query:

SELECT LEFT(Line1,20), RIGHT(Line,20) FROM minde_candidate_workexp
    WHERE minde_candidate_workexp.WorkDesc LIKE '%Accounts%' ;

which will display first 20 character of line in which keyword "Account" appear and last 20 character of the line.

score 2 · Accepted Answer · edited May 23 '17 at 12:04

A combination of LOCATE() and SUBSTRING() should do the trick.

Something like this (untested):

SELECT 
  SUBSTRING(m.WorkDesc, 
            GREATEST(LOCATE('Accounts', m.WorkDesc) - 20, 0), -- Force pos >= 0
            LENGTH('Accounts') + 20) 
FROM minde_candidate_workexp m 
WHERE 
  m.WorkDesc LIKE '%Accounts%' 
:

See also this similar question: Extract X number of words surrounding a given search string within a string - it sounds like you want to pull some context out of a description, it might be more useful to do that on word boundaries than on a fixed number of letters.

score 1 · Answer 2 · answered Apr 06 '12 at 14:05

The following demonstrates one idea:

SELECT substring(line1, locate('account',line1) - 20, 20), 
       substring(line1, locate('account',line1) + length('account'), 20) 
   FROM ...

It assumes, though, that there are at least 20 characters in front of 'account'. It would likely need to check for that otherwise the value could be negative, which produces different results (a negative position is an offset from the end of the string).

Find 1st and last 20 character after particular keyword from database?

2 Answers2