to remove the last column

Hi Guys,

I want to remove the last column of my file..
My file looks like this..

UPDATE TRDSTG.STRDCLM2 SET C_TREAD_COMPONENT='NR', X_MEMO_REF='M:LOP8 F 2009' WHERE C_SOURCE='CSC' AND D_QTR_APPLBTY=200902 AND I_DOCUMENT=381917678 AND C_TREAD_COMPONENT='GP' AND C_SFTY_CSQ='W08J01182' AND X_MEMO_REF='G:LOP8 F 2009';

I want it as

UPDATE TRDSTG.STRDCLM2 SET C_TREAD_COMPONENT='NR', X_MEMO_REF='M:LOP8 F 2009' WHERE C_SOURCE='CSC' AND D_QTR_APPLBTY=200902 AND I_DOCUMENT=381917678 AND C_TREAD_COMPONENT='GP' AND C_SFTY_CSQ='W08J01182'

I tried cut command.. but of no use..
This is the command i tried

cut -d ' ' -f1,17

Regards,
Magesh

---------- Post updated at 07:52 PM ---------- Previous update was at 07:39 PM ----------

Guys. i did it in a very crude way...

sed 's/................................$//g' a.SQL > new_file.sql

Please let me knw if it can be done by awk..

nawk -F'AND' -v OFS='AND' '{NF=NF-1;$1=$1;print}' myFile

Try...

 
awk -F "AND" '!NF' inputfile

I get nothing. Have you 'tried' it?

With cut:

cut -d' '  -f1-17 file

Guys,

@Vgresh99, i am still getting the whole line with the last column

p01: head -1 UPDATE.GCS.200902.LOP8.SQL |awk -F'AND' -v OFS='AND' '{NF=NF-1;$1=$1;print}'
UPDATE TRDSTG.STRDCLM2 SET C_TREAD_COMPONENT='06', X_MEMO_REF='M:LOP8 R 2008' WHERE C_SOURCE='GCS' AND D_QTR_APPLBTY=200902 AND I_DOCUMENT=386543609 AND C_TREAD_COMPONENT='GP' AND C_SFTY_CSQ='W09830102' AND X_MEMO_REF='G:LOP8 R 2008';

@malcomex, i am not getting any output itself

@ necroman,,your code worked fine..

Or, with Perl:

$
$ cat mac.txt
UPDATE TRDSTG.STRDCLM2 SET C_TREAD_COMPONENT='NR', X_MEMO_REF='M:LOP8 F 2009' WHERE C_SOURCE='CSC' AND D_QTR_APPLBTY=200902 AND I_DOCUMENT=381917678 AND C_TREAD_COMPONENT='GP' AND C_SFTY_CSQ='W08J01182' AND X_MEMO_REF='G:LOP8 F 2009';
$
$ perl -lne 's/(.*) AND.*/$1/; print' mac.txt
UPDATE TRDSTG.STRDCLM2 SET C_TREAD_COMPONENT='NR', X_MEMO_REF='M:LOP8 F 2009' WHERE C_SOURCE='CSC' AND D_QTR_APPLBTY=200902 AND I_DOCUMENT=381917678 AND C_TREAD_COMPONENT='GP' AND C_SFTY_CSQ='W08J01182'
$

tyler_durden

Awk clause.. but it assumes that the bit to be wacked is an "AND" clause....

{
n=split($0, line)
for(i = n; i > 0 ; i--)
if ( line [i]== "AND")
break;
for(n = 1; n < i; n++)
printf "%s ", line[n]

    print ";"

}

perl:

perl -ne '{s/AND(?!.*AND.*$).*//;print;}' 

Guys,, both the perl and awk commands worked fine.. actually i do not know perl..

@jp2542a, can you please explain your awk command.. that really help me in better understanding of awk.. i am learning awk..

Thanks for your help guys

Just bash and parameter expansion :wink:

#!/bin/bash
# read line < file

# echo $line
UPDATE TRDSTG.STRDCLM2 SET C_TREAD_COMPONENT='NR', X_MEMO_REF='M:LOP8 F 2009' WHERE C_SOURCE='CSC' AND D_QTR_APPLBTY=200902 AND I_DOCUMENT=381917678 AND C_TREAD_COMPONENT='GP' AND C_SFTY_CSQ='W08J01182' AND X_MEMO_REF='G:LOP8 F 2009';

# echo ${line%\ AND\ *}
UPDATE TRDSTG.STRDCLM2 SET C_TREAD_COMPONENT='NR', X_MEMO_REF='M:LOP8 F 2009' WHERE C_SOURCE='CSC' AND D_QTR_APPLBTY=200902 AND I_DOCUMENT=381917678 AND C_TREAD_COMPONENT='GP' AND C_SFTY_CSQ='W08J01182'

cool danmero..

Can you tell me what your code does?

@jp2542a, using split command spliting the line and storing the number of words in n
and after that you are reducing the value of i until u get AND and then your printing before AND. Please correct me if i am wrong..

See : How can I use parameter expansion? How can I get substrings? How can I get a file without its extension, or get just a file's extension?