Removing last occurance of string in text

Shell Programming and Scripting
1

I have text file as follows and would like to remove the last occurance of "UNION ALL" string and replace @@ with single quote (').

Input text in file is

with temp as ( 
( select -----------  where OPERATION = @@B@@ and OBJECTTYPE = @@P@@ and  start_time desc ) UNION ALL 
( select -----------  where OPERATION = @@B@@ and OBJECTTYPE = @@P@@ and  start_time desc ) UNION ALL 
( select -----------  where OPERATION = @@B@@ and OBJECTTYPE = @@P@@ and  start_time desc ) UNION ALL 
( select -----------  where OPERATION = @@B@@ and OBJECTTYPE = @@P@@ and  start_time desc ) UNION ALL 
) 
select xxxx, yyyyyy from temp with ur  ;

Output expected is

with temp as ( 
( select -----------  where OPERATION = 'B' and OBJECTTYPE = 'P' and  start_time desc ) UNION ALL 
( select -----------  where OPERATION = 'B' and OBJECTTYPE = 'P' and  start_time desc ) UNION ALL 
( select -----------  where OPERATION = 'B' and OBJECTTYPE = 'P' and  start_time desc ) UNION ALL 
( select -----------  where OPERATION = 'B' and OBJECTTYPE = 'P' and  start_time desc ) 
) 
select xxxx, yyyyyy from temp with ur  ;

Appreciate your help .

2

Try:

perl -p0e 's/@@/\x27/g;s/UNION ALL\s+\n\)/\n)/s' file
3

Thanks .. anythig using sed or awk commands or any native unix commands.

4

What do you suggest is the best indication of the last line of "UNION ALL"?

5

Try this:

awk -v q="'" '/UNION ALL$/{
  gsub("@@",q); if(s)print s; s=$0; f=1; next}
f{
  sub(" UNION ALL$",x,s) ;print s;f=0; s=""
}
1' file
6 
ed -s file <<'EOED' >/dev/null
1,$s/@@/'/g
1
?UNION ALL?s/UNION ALL//
w
q
EOED

Regards,
Alister

7

Am I doing something wrong , it's not removing the last occurance of "UNION ALL" . lets think about only removing the last occurance of "UNION ALL"

Code is:

#! /usr/bin/ksh
. ~/.profile

awk -v q="'" '/UNION ALL$/{
  gsub("@@",q); if(s)print s; s=$0; f=1; next}
f{
  sub(" UNION ALL$",x,s) ;print s;f=0; s=""
}
1' text1.txt    >  text_output.txt

Infile text.txt is:

with temp as ( 
( select -----------  where OPERATION = @@B@@ and OBJECTTYPE = @@P@@ and  start_time desc ) UNION ALL 
( select -----------  where OPERATION = @@B@@ and OBJECTTYPE = @@P@@ and  start_time desc ) UNION ALL 
( select -----------  where OPERATION = @@B@@ and OBJECTTYPE = @@P@@ and  start_time desc ) UNION ALL 
( select -----------  where OPERATION = @@B@@ and OBJECTTYPE = @@P@@ and  start_time desc ) UNION ALL  <------- remove this 
) 
select xxxx, yyyyyy from temp with ur  ;

Output file text_output.txt looks like:

with temp as ( 
( select -----------  where OPERATION = @@B@@ and OBJECTTYPE = @@P@@ and  start_time desc ) UNION ALL 
( select -----------  where OPERATION = @@B@@ and OBJECTTYPE = @@P@@ and  start_time desc ) UNION ALL 
( select -----------  where OPERATION = @@B@@ and OBJECTTYPE = @@P@@ and  start_time desc ) UNION ALL 
( select -----------  where OPERATION = @@B@@ and OBJECTTYPE = @@P@@ and  start_time desc ) UNION ALL 
) 
select xxxx, yyyyyy from temp with ur
8

You have a space at the end of the lines, remove the '$' sign after UNION ALL:

awk -v q="'" '/UNION ALL/{
  gsub("@@",q); if(s)print s; s=$0; f=1; next}
f{
  sub(" UNION ALL",x,s) ;print s;f=0; s=""
}
1' file
9

The "awk" solution will delete the last line for the input:

select xxxx, yyyyyy from temp with ur ;
( select ----------- where OPERATION = '5' and OBJECTTYPE = 'P' and start_time desc ) UNION ALL
( select ----------- where OPERATION = '6' and OBJECTTYPE = 'P' and start_time desc ) UNION ALL
( select ----------- where OPERATION = '7' and OBJECTTYPE = 'P' and start_time desc ) UNION ALL )

The following code will remove the last occurrence of "UNION ALL":

#!/usr/bin/ksh
mLast='FIRST'
mUA='UNION ALL'
mLastFound='N'
while read mLine; do
  mCnt=$(echo ${mLine} | egrep -c "${mUA}")
  if [[ "${mCnt}" = "0" ]]; then
    mFound='N'
  else
    mFound='Y'
  fi
  if [[ "${mFound}" = "N" ]]; then
    if [[ "${mLastFound}" = "Y" ]]; then
      mLast=$(echo ${mLast} | sed "s/${mUA}//")
    fi
  fi
  if [[ "${mLast}" != "FIRST" ]]; then
    echo ${mLast}
  fi
  mLast=${mLine}
  mLastFound=${mFound}
done < Input_File
if [[ "${mLast}" != "FIRST" ]]; then
  if [[ "${mLastFound}" = "Y" ]]; then
    mLast=$(echo ${mLast} | sed "s/${mUA}//")
  fi
  echo ${mLast}
fi
10 
 $(cat file);echo $data | rev | sed "s/LLA NOINU//;s/@@/\'/g" | rev