Find First 4 words and Last 4 words from database after particular keywords?

Question

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

Currently I am using this query for finding first 20 characters and last 20 characters after particular keyword.

SELECT 
SUBSTRING(line.WD, LOCATE('%Account%', line.WD) - 20, 20) AS First20Char, 
SUBSTRING(line.WD, LOCATE('%Account%', line.WD)+LENGTH('a'), 20) AS Last20Char 
FROM `line` WHERE line.WD LIKE '%Account%'

Answer 1

SQL is called *S*QL for a reason -- you aren't going to be able to do something this advanced without the help of an intermediary programming language --- unless you want things to get messy.

The short answer is that you will have to use a UDF (user defined function) to first split, then parse your delimited string (on spaces ' ') then you can apply your -4/+4 logic.

Here is the best article on the net on SQL-UDFs to get you started:

http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str

Good luck!

PS -- it'll be a lot easier to just grab your +/-30 chr then do the parsing externally though =]. It'll probably save more system resources too.