Selectively search and replace certain lines using a regular expression

Posted by eneveu on Stack Overflow See other posts from Stack Overflow or by eneveu
Published on 2010-05-27T10:54:59Z Indexed on 2010/05/28 6:11 UTC
Read the original article Hit count: 229

Filed under:
|
|
|
|

I have a file containing a lot of SQL statements, such as:

CREATE TABLE "USER" (
    "ID" INTEGER PRIMARY KEY,
    "NAME" CHARACTER VARYING(50) NOT NULL,
    "AGE" INTEGER NOT NULL
);

COPY "USER" (id, name, age) FROM stdin;
1   Skywalker   19
2   Kenobi      57

I want the column names in the COPY statements to be uppercased and quoted:

COPY "USER" ("ID", "NAME", "AGE") FROM stdin;

Using sed, I found the following regexp:

sed -r 's/([( ])(\w+)([,)])/\1"\U\2\E"\3/g'

It does replace the column names, but it is not selective enough, and replaces other words in the file:

~/test]$sed -r 's/([( ])(\w+)([,)])/\1"\U\2\E"\3/g' star_wars_example
CREATE TABLE "USER" (
  "ID" INTEGER PRIMARY "KEY",
  "NAME" CHARACTER VARYING("50")NOT "NULL",
  "AGE" INTEGER NOT NULL
);

COPY "USER" ("ID", "NAME", "AGE") FROM stdin;
1   Skywalker   19
2   Kenobi      57

To avoid this problem, I want sed to only apply my regexp to the lines starting with COPY and ending with FROM stdin;.

I have looked into lookahead / lookbehind, but they are not supported in sed. They seem to be supported in super-sed, but I am currently using Cygwin (Windows is mandatory here...) and it does not seem available in the package list.

Is there a way to force sed to only consider specific line?

I've considered piping my file through grep before applying sed, but other lines will then disappear from the output.

Am I missing something obvious?

It would be great if the answer was easily applicable on a default Cygwin install. I guess I could try installing super-sed on cygwin, but I'd like to know if there are more obvious ideas

© Stack Overflow or respective owner

Related posts about regex

Related posts about sed