Words before and after selected text

Question

My SQL query looks like this...

sekect * FROM mail_headers a LEFT JOIN mail_headers_body b ON a.mailid=b.id blah blah blah

I can select the lines before and after the result, using -A, -B switches. But how do I find the 4 words before and after the selected text?

grep -i 'JOIN mail_headers_body' mysql-gen.log

This will return the query mentioned above. But what I want to know is 4 "words" before the selected text. i.e.

FROM mail_headers a LEFT

And 4 (or 5) words after...

b ON a.mailid=b.id blah

Answer 1

you can use awk,

$ echo $sql|awk -F"JOIN mail_headers_body" '{ 
    m=split($1,a," ");n=split($2,b," ")
    print a[m-3],a[m-2],a[m-1],a[m]; print b[1],b[2],b[3],b[4] }'

this is assuming you have only 1 "JOIN mail_headers_body". Otherwise, use a for loop to iterate the fields.

Answer 2

Try this:

grep -Eio '( *[^ ]* *){4}JOIN mail_headers_body( *[^ ]* *){4}'

It should give you this output:

 FROM mail_headers a LEFT JOIN mail_headers_body b ON a.mailid=b.id blah

Answer 3

Here is the AWK, GREP, and CAT combinations in a one-liner:

cat YOURFILE | grep -i 'JOIN mail_headers_body' | awk '{ for(i;i<=NF;i++){ if($i ~ /JOIN/) { a = i } } for(x=4;x!=0;x--){ before=before" "$(a-x)} a+=3; for(x=0;x<4;x++) { after=after" "$(a+x) } print "BEFORE: "before" \nAFTER: "after  }'

update: I just removed a--; from the awk. I just realized it was skipping the first word before JOIN which is LEFT.