CODEWITHSUNDEEP
awksed
Yevhen Orestov
Yevhen Orestov

Reputation: 77

How to replace in file SQL specific lines?

I tried some sed & awk prior to asking with no success so here I am.

What I am looking to do it to replace some lines in place (in .sql file) in bash script. The problem is as I see it with SQL specific backticks & possibly with single quotes?

Actual thing to do is to replace lines like

`payment_methods` text COLLATE utf8_unicode_ci DEFAULT '-1',

to

`payment_methods` text COLLATE utf8_unicode_ci,

SQL dump is ~10 Gb.

sed -i 's/`payment_methods` text COLLATE utf8_unicode_ci DEFAULT '-1',/`payment_methods` text COLLATE utf8_unicode_ci,/' file.sql

or using the line number:

sed -i '357s/.*/`payment_methods` text COLLATE utf8_unicode_ci,/' file.sql

awk 'NR==357 {$0="`payment_methods` text COLLATE utf8_unicode_ci,"} 1' file.sql

Also forgot to mention that using backslashes on escape characters did not help.

Upvotes: 1

Views: 208

Answers (2)

Raman Sailopal
Raman Sailopal

Reputation: 12877

Using sed:

sed -rn "357s/(^.*payment_methods.*text COLLATE utf8_unicode_ci)( DEFAULT.*-1.*)(,$)/\1\3/p" file

Split line 357 into 3 sections based on regular expressions and then substitute the line for the first and the third sections, printing the result.

To commit the changes back to the file, add -i and so:

sed -ri "357s/(^.*payment_methods.*text COLLATE utf8_unicode_ci)( DEFAULT.*-1.*)(,$)/\1\3/" file

Upvotes: 0

RavinderSingh13
RavinderSingh13

Reputation: 133528

With your shown samples, could you please try following.

awk 'FNR==357{sub(/ DEFAULT \047-1\047,/,"")} 1' Input_file

OR in case you want to verify contents of 357th line too along with its line number then try following.

awk '
FNR==357 && /`payment_methods` text COLLATE utf8_unicode_ci DEFAULT \047-1\047,/{
  sub(/ DEFAULT \047-1\047,/,"")
}
1' Input_file

NOTE: This will print the lines on terminal, once you are happy with results then you could append > temp && mv temp Input_file to above solution to save output into Input_file itself.

Upvotes: 4

Related Questions