CODEWITHSUNDEEP
regexawksed
Antonio Ortells
Antonio Ortells

Reputation: 337

sed regex for multiple occurrences of capture group

I have an SQL file that consists of multiple lines like this:

INSERT INTO mytable VALUES (1,2,3,'foo'),(2,4,5,'bar'),(3,5,9,'baz'),...;
INSERT INTO mytable VALUES (10,2,3,'foo'),(11,4,5,'bar'),(12,5,9,'baz'),...;

And I want to add a fifth column to each tuple in each line, like this

INSERT INTO mytable VALUES (1,2,3,'foo','txt_foo'),(2,4,5,'bar','txt_bar'),(3,5,9,'baz','txt_baz'),...;
INSERT INTO mytable VALUES (10,2,3,'baz','txt_baz'),(11,4,5,'bar', 'txt_bar'),(12,5,9,'foo','txt_foo'),...;

What would be the sed or awk command that would get the job done? I tried multiple regex combinations, but it does not seem to pick any of the occurrences or it picks the whole line.

I tried, for instance:

sed -E "s/\((d+),(d+),(d+),'(.*)'\)/(\1,\2,\3,'\4','txt_\4')/g" /myfile.sql

And it does nothing.

Upvotes: 2

Views: 434

Answers (2)

stack0114106
stack0114106

Reputation: 8711

You can try Perl also. Here $q stores the single quotes in hex form \x27

$ cat antonio.sql
INSERT INTO mytable VALUES (1,2,3,'foo'),(2,4,5,'bar'),(3,5,9,'baz')
INSERT INTO mytable VALUES (10,2,3,'foo'),(11,4,5,'bar'),(12,5,9,'baz')

$ perl -pe ' $q="\x27"; s/$q(.+?)$q\)/$q$1$q,${q}txt_$1$q\)/g  ' antonio.sql
INSERT INTO mytable VALUES (1,2,3,'foo','txt_foo'),(2,4,5,'bar','txt_bar'),(3,5,9,'baz','txt_baz')
INSERT INTO mytable VALUES (10,2,3,'foo','txt_foo'),(11,4,5,'bar','txt_bar'),(12,5,9,'baz','txt_baz')

$

Upvotes: 1

anubhava
anubhava

Reputation: 785068

You may use this sed:

sed -E "s/'([^']+)'\)/'\1', 'txt_\1')/g" file


INSERT INTO mytable VALUES (1,2,3,'foo', 'txt_foo'),(2,4,5,'bar', 'txt_bar'),(3,5,9,'baz', 'txt_baz'),...;
INSERT INTO mytable VALUES (10,2,3,'foo', 'txt_foo'),(11,4,5,'bar', 'txt_bar'),(12,5,9,'baz', 'txt_baz'),...;

Upvotes: 5

Related Questions