Replace line before pattern using sed or awk

Question

I have the following input in a sacro.sql file:

{ TABLE "informix".sacro_log row size = 64 number of columns = 3 index size = 0 }

{ unload file name = sacro00518.unl number of rows = 0 }

create table "informix".sacro_log
(
log_id serial not null constraint "informix".nnc_sac_log00,
log_type integer,
log_data text
);

revoke all on "informix".sacro_log from "public" as "informix";

I want to replace 2nd line above "create table "informix".sacro_log" with test

Example output I want:

{ TABLE "informix".sacro_log row size = 64 number of columns = 3 index size = 0 }

test

create table "informix".sacro_log
(
log_id serial not null constraint "informix".nnc_sac_log00,
log_type integer,
log_data text
);

revoke all on "informix".sacro_log from "public" as "informix";

sed -z 's/[^\n]*$[[:space:]]*create$/test\1/' sacro.sql – Costas Apr 30 '17 at 06:12 — Costas, Apr 30 '17 at 06:12
Unfortunately the sed -z option is not valid in HP-UX – Christopher Karsten Apr 30 '17 at 18:48 — Christopher Karsten, Apr 30 '17 at 18:48

Kusalananda · Answer 1 · 2017-04-30T14:49:02.680

1

sed '/create table "informix"/i\
test
' data.in >data.out

This will create data.out with the following contents:

{ TABLE "informix".sacro_log row size = 64 number of columns = 3 index size = 0 }

{ unload file name = sacro00518.unl number of rows = 0 }

test
create table "informix".sacro_log
(
log_id serial not null constraint "informix".nnc_sac_log00,
log_type integer,
log_data text
);

The i ("insert") command in sed will insert the specified text before the matched pattern. The text inserted must follow a literal newline (GNU sed accepts sed '/pattern/i text' too).

edited Apr 30 '17 at 14:49

answered Apr 30 '17 at 07:28

Kusalananda

333,661

How can i use a variable to search and replace above using sed '/create table "informix"/i
test ' data.in >data.out – Christopher Karsten Apr 30 '17 at 18:47
@ChristopherKarsten You should be able to sed "/$pattern/i\ $text" data.in. Note the use of double rather single quotes. – Kusalananda Apr 30 '17 at 19:13

score 0 · Answer 2 · answered Jan 02 '19 at 21:44

I do not know if yu are still waiting for an answer, this could help.

awk -v newline=test -v trigger='create table "informix"' '
   {a[NR]=$0}
   NR>2 { if (index($0,trigger)>0) { a[NR-2]=newline; }}
   END { 
          for (i=1;i<=NR;i++) { print a[i] }
       }
   ' sacro.sql

Replace line before pattern using sed or awk

2 Answers2

Linked