Duplicating a few lines with a modification

[IMAUser]

Hi ,

I have a huge file with a lot of oracle statements. In there is there a way to do the following

When I come across the text "_OLD_FK" like below lines
ALTER TABLE TEST$AB DROP CONSTRAINT TEST_OLD_FK
/

I need to add another set of lines with the _OLD_FK replaced with _NEW_FK

so the resultant file should have lines as below

ALTER TABLE TEST$AB DROP CONSTRAINT TEST_OLD_FK
/
ALTER TABLE TEST$AB DROP CONSTRAINT TEST_NEW_FK
/

Any ideas folks...

Thanks in advance.....

 

[PHV]

And what have you tried so far ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[feherke]

Hi

If that TEST_OLD_FK is always the last work in the line, this will work :

awk 'print;$NF=="TEST_OLD_FK"{$NF="TEST_NEW_FK";d=$0;getline;print;print d"\n/"}' /input/file

If not, this is similar but not depends on the word's position. But is slower.

awk 'print;/TEST_OLD_FK/{gsub(/_OLD_FK/,"_NEW_FK");d=$0;getline;print;print d"\n/"}' /input/file

Feherke.

http://rootshell.be/~feherke/

 

[IMAUser]

Thanks Feherke, but I get the error on both the pieces of code.

awk: syntax error near line 1
awk: bailing out near line 1

Is there a way of debugging...When I got the errors I tried to use nawk instead of awk. But no luck there.

 

[feherke]

Hi

Oops. Of course you have.

awk '[red]{[/red]print[red]}[/red];$NF=="TEST_OLD_FK"{$NF="TEST_NEW_FK";d=$0;getline;print;print d"\n/"}' /input/file

[gray]# or [/gray]

awk '[red]{[/red]print[red]}[/red];/TEST_OLD_FK/{gsub(/_OLD_FK/,"_NEW_FK");d=$0;getline;print;print d"\n/"}' /input/file

Sorry, that was because a last minute change. My original was :

awk '[red]1[/red];$NF=="TEST_OLD_FK"{$NF="TEST_NEW_FK";d=$0;getline;print;print d"\n/"}' /input/file

[gray]# or [/gray]

awk '[red]1[/red];/TEST_OLD_FK/{gsub(/_OLD_FK/,"_NEW_FK");d=$0;getline;print;print d"\n/"}' /input/file

Feherke.

http://rootshell.be/~feherke/

 

[IMAUser]

That does the trick, though I have used the second code with the gsub.

A question on the first code: I thougth NF was for number of fields in a record, but you seem to be matching a text. I guess you can use it only if the text is the last word ( as you said).

Many Thanks.

 

[feherke]

Hi

I thougth NF was for number of fields in a record

Correct. [tt]$1[/tt] = first field, [tt]$2[/tt] = second field , ..., [tt]$(NF-1)[/tt] = penultimate field, [tt]$NF[/tt] = last field. So after the dollar sign ( [tt]$[/tt] ) can be a variable or expression too, like when stepping through the fields :

for (i=1;i<=NF;i++) print "field " i " has value " $i

Feherke.

http://rootshell.be/~feherke/

 

[IMAUser]

I have hit a snag. I cannot look for TEST_OLD_FK

but need to look for

DROP CONSTRAINT *_OLD_FK, where * can be anything. I mean it could be AAA_OLD_FK or BBBBBB_OLD_FK or CC_OLD_FK. I tried using a * or even a regex like [A-Z]* but that didnt work. What am I doing wrong.

 

[feherke]

Hi

Sorry, this one I can solve nicely only with [tt]gawk[/tt], as [tt]/[A-Z]+_OLD_FK/[/tt] will store in the [tt]&[/tt] the whole expression, with the static part too.

awk '1;/[A-Z]+_OLD_FK/{d=gensub(/([A-Z]+)_OLD_FK/,"\\1_NEW_FK","g");getline;print;print d"\n/"}' /input/file

Feherke.

http://rootshell.be/~feherke/

 

[feherke]

Hi

Hmm... Can that _OLD_FK we involved in other thing than you need to replace ? I think this would be enough :

awk '1;/[A-Z]+_OLD_FK/{gsub(/_OLD_FK/,"_NEW_FK");d=$0;getline;print;print d"\n/"}' /input/file

Feherke.

http://rootshell.be/~feherke/