How to print lines from a files with specific start and end patterns and pick only the last lines?

UNIX for Beginners Q & A
1

Hi, I need to print lines which are matching with start pattern "SELECT" and END PATTERN ";" and only select the last "select" statement including the ";" .
I have attached sample input file and the desired input should be as:

INPUT FORMAT:

SELECT  
ABCD,
DEFGH,
DFGHJ,
JKLMN,
AXCVB,
DFVBNM,
QWER,
RTU,
TYUI,
IOPL from test1
limit 1;

delete from test2;

SELECT  ABCD,
DEFGH,
DFGHJ,
JKLMN,
AXCVB,
DFVBNM,
QWER,
RTU,
TYUI,
IOPL from test1
limit 1;
create table as SELECT  
ABCD,
DEFGH,
DFGHJ,
JKLMN,
AXCVB,
DFVBNM,
QWER,
RTU,
TYUI,
IOPL from test1
limit 1;

SELECT  ABCD,
DEFGH,
DFGHJ,
JKLMN,
AXCVB,
DFVBNM,
QWER,
RTU,
TYUI,
IOPL from test1
limit 1;


SELECT 
ABCD,
DEFGH,
DFGHJ,
JKLMN,
AXCVB,
DFVBNM,
QWER,
RTU,
TYUI,
IOPL from test1
limit 1;

SELECT  
ABCD,
DEFGH,
from test1
limit 1;

delete from test2;

create table as SELECT  
ABCD,
DEFGH,
DFGHJ,
JKLMN,
AXCVB,
DFVBNM,
QWER,
RTU,
TYUI,
IOPL from test1345
limit 1;

create table as SELECT  ABCD,
DEFGH,
DFGHJ,
JKLMN,
AXCVB,
DFVBNM,
QWER,
RTU,
TYUI,
IOPL from test123
limit 1;

OUTPUT FORMAT

SELECT  
ABCD,
DEFGH,
from test1
limit 1;
2

Hi, try:

awk '$1=="SELECT"{s=$0} END{if(s) print s}' RS= file
1 Like
3

It worked Thanks

5

Hi, Now do i remove the output lines only (staring from start to ; ) from the input file

6

Try this:

awk '
$1=="SELECT" {
   printf "%s%s", s, r
   s=$0 RS
   r=""
   next
}
{ r=r $0 RS }
END { 
   gsub(RS"$", "",r)
   printf "%s", r 
}' RS=\; file
1 Like
7

Another approach is to make two passes of the file, in the first identify the record to be skipped and in the 2nd print everything else:

awk '
FNR==1 {file++}
file==1 && $1=="SELECT"{skip=FNR}
file==2 && NF && FNR!=skip
' RS=\; ORS=\; file file