replacing only last occurrence conditionally with awk / sed

manish26 · 02-07-2017, 08:50 AM

hello experts

I have got a below situation in a Unix file..

cat file1

CREATE table ( col1 int) ;
ADD ( CONSTRAINT myconst1 FOREIGN KEY(PID) REFERENCES MYTABLE );
ADD ( CONSTRAINT myconst1 FOREIGN KEY(PID) REFERENCES MYTABLE )
;

What I want to do now is this

(1) remove ( symbol from any line where ( symbol comes between ADD and CONSTRAINT clause 'ADD ( CONSTRAINT'
(2) Remove only last occurrence of ) symbol from any line containing word REFERENCES

for example this line
ADD ( CONSTRAINT myconst1 FOREIGN KEY(PID) REFERENCES MYTABLE );

should be converted to

ADD CONSTRAINT myconst1 FOREIGN KEY(PID) REFERENCES MYTABLE ;

appreciate your help

grail · 02-07-2017, 09:30 AM

Sounds like a plan ... what have you done to attempt to solve this and where are you stuck?

manish26 · 02-07-2017, 09:34 AM

this is what I tried

awk '{sub(/ADD.*CONSTRAINT/, "ADD " "CONSTRAINT"); print} | awk '/REFERENCES/ {sub(/\)/,"")}1'

seems like first part is working but second part of awk is not giving me desired output

danielbmartin · 02-07-2017, 10:05 AM

Quote:

Originally Posted by manish26

this is what I tried

awk '{sub(/ADD.*CONSTRAINT/, "ADD " "CONSTRAINT"); print} | awk '/REFERENCES/ {sub(/\)/,"")}1'

Your solution is almost right. Try this variation:

Code:

awk '{sub(/ADD.*CONSTRAINT/, "ADD CONSTRAINT")
    /REFERENCES/ sub(/\);$/,";")}1' $InFile >$OutFile

Note that only one awk is needed.

Daniel B. Martin

grail · 02-07-2017, 10:35 AM

I would warn against the issues of using '.*' when trying to catch specific characters, for example (however unlikely), your current example to remove a single '(' from between ADD and CONSTRAINT:

Code:

ADD ( CONSTRAINT myconst1 FOREIGN KEY(PID) REFERENCES MYTABLE CONSTRAINT myconst1 FOREIGN KEY(PID) REFERENCES MYTABLE );
# output of your awk
ADD CONSTRAINT myconst1 FOREIGN KEY(PID) REFERENCES MYTABLE );

As you can see, if there was any pertinent data to be kept between ADD and second CONSTRAINT it has been lost.

As there is now columns to really worry about, I would probably used sed. You can use your same 2 substitutions (Daniel's work better) and use the -e option to submit multiple changes to the same row.
You could also do it all in one sed but it is pretty messy (maybe one of the sed gurus can tidy it up:

Code:

sed -r 's/((ADD)[^(]*\(([^C]*CONSTRAINT)|\)(.?)$)/\2\3\4/g'

danielbmartin · 02-07-2017, 10:47 AM

Quote:

Originally Posted by manish26

What I want to do now is this

(1) remove ( symbol from any line where ( symbol comes between ADD and CONSTRAINT clause 'ADD ( CONSTRAINT'
(2) Remove only last occurrence of ) symbol from any line containing word REFERENCES

I wonder if you want to change a line if and only if both conditions are true. If this is the case the solutions already posted may be inadequate.

Daniel B. Martin

manish26 · 02-07-2017, 11:08 AM

>>I wonder if you want to change a line if and only if both conditions are true. If this is the case the solutions already posted may be inadequate.

Not really, it is possible that there is no REFERENCES clause in some lines but ADD ( CONSTRAINTS is still there
Also please note that there is no pertinent data or any string between ADD ( CONSTRAINTS

Thanks all, I will try your suggested answers and get back with results

MadeInGermany · 02-08-2017, 03:15 PM

The following might be better readable

Code:

sed '
s/\(ADD\) *[(] *\(CONSTRAINT\)/\1 \2/
s/\(REFERENCES.*\)[)]/\1/
' file1