How to preserve the same text and modify the same text to append?

Hi,

I have an issue modifying a file. Below is the content of the file.

File name - a.txt

SELECT LISTAGG(SCORE),   LISTAGG(VB), 
	   LISTAGG(SCORE*12 + 3),
	   LISTAGG(DOB) 
	   from T_CONCAT ;
	   
SELECT LISTAGG(SCORE),
       LISTAGG(VB),
	   LISTAGG(SCORE*100 + 3),
	   from T_CONCAT ;	 

This is what I desired to come:-

--ignore begin
SELECT LISTAGG(SCORE),   LISTAGG(VB), 
	   LISTAGG(SCORE*12 + 3),
	   LISTAGG(DOB) 
	   from T_CONCAT ;
--ignore end

SELECT CountMannuArray(LISTAGG(SCORE)),
       CountMannuArray(LISTAGG(VB)),
	   CountMannuArray(LISTAGG(SCORE*12 + 3)),
	   CountMannuArray(LISTAGG(DOB)) 
	   from T_CONCAT ;

--ignore begin	   
SELECT LISTAGG(SCORE),
       LISTAGG(VB),
	   LISTAGG(SCORE*100 + 3),
	   from T_CONCAT ;	   
--ignore end

SELECT CountMannuArray(LISTAGG(SCORE)),
       CountMannuArray(LISTAGG(VB)),
	   CountMannuArray(LISTAGG(SCORE*100 + 3)),
	   CountMannuArray(LISTAGG(DOB)) 
	   from T_CONCAT ;

I find it difficult to preserve the same text and put it in --ignore begin and --ignore end clause.

Other one is achieved by

sed -i  's/LISTAGG/CountMannuArray(LISTAGG/g;s/)/))/g' a.txt

Please advice.

Hi, try:

awk '
  /SELECT *LISTAGG/ {
    printf "--ignore begin\n%s\n--ignore end\n\n", $0
    gsub(/LISTAGG[^)]*\)/,"CountMannuArray(&)")
  }
  {
    print
  }
' RS= ORS='\n\n' file

This only works if there are really no spaces on the empty lines that separate the SELECT statements.

2 Likes