sed replace text matching complicated regex pattern

Question

I am in the process of porting an existing database schema to Postgresql.

I need to replace occurrences of the word 'go' with a semi comma.

I have noticed that the word 'go' appears in the text, in the following pattern:

[non empty string (SQL)]
[followed by one or more new lines]
[followed by one or more white space]
[followed by the word 'go']
[Followed by one or more new lines]

I want to replace the above pattern with the following one:

[non empty string (SQL)]
[followed by ';']
[Followed by TWO new lines]

I am trying to build a regex expression which I can use with sed, to perform the replacement described above - but I am relatively new to regex.

For the purpose of clarity, I have included sample text BEFORE and AFTER the substitution I want to achieve:

-- Original File contents below -------



go
CREATE TABLE foobar
(
    f1    INT,
    f2    INT,
    f3    FLOAT,
        f4    VARCHAR(32) NOT NULL,
    f5    INT,
    f6    datetime,
        f7    smallint
)


go

GRANT UPDATE, INSERT, DELETE, SELECT ON foobar TO dbusr
go
CREATE UNIQUE INDEX idxu_foobar ON foobar (f1, f2)

go


--- REPLACED FILE CONTENTS -----------



go
CREATE TABLE foobar
(
    f1    INT,
    f2    INT,
    f3    FLOAT,
        f4    VARCHAR(32) NOT NULL,
    f5    INT,
    f6    datetime,
        f7    smallint
);

GRANT UPDATE, INSERT, DELETE, SELECT ON foobar TO dbusr;
CREATE UNIQUE INDEX idxu_foobar ON foobar (f1, f2);

Can anyone help with the expression to use to achieve this, so I can execute: sed -i 's/original_match_expr/replacement_expr/g' myfile.sql

FWIW, Simply replacing the word 'go' with ';' will result in valid SQL statements, as newlines and spaces are generally irrelevant when running scripts against a Postgresql db (eg. "psql -d <yourdb> -U <youruser> -f <yourscript>") — bma
– bma, Commented Nov 2, 2013 at 20:13

iruvar · Accepted Answer · 2013-11-02 20:54:39Z

1

With gawk

awk -v RS='\\s*go' '{print $0""(RT ~ /go/? ";\n\n": "")}' file.txt

The record separator RS is set to 0 or more space characters followed by go. GNU awk then treats the block of text between two successive instances of record separators as a record. So print the record followed by a custom record separator (; followed by two newlines)

edited Nov 2, 2013 at 20:54

answered Nov 2, 2013 at 20:21

iruvar

23.5k7 gold badges58 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Homunculus Reticulli Over a year ago

+1 for the one liner - but I have never used awk before, could you please explain what' going on in the expression?

iruvar Over a year ago

@HomunculusReticulli, added some explanation.

Birei · Accepted Answer · 2013-11-02 21:02:48Z

1

Try following solution using the GNU version of sed:

sed -ne ':a; $! { N; ba }; s/\([^[:space:]]\)[[:space:]]*go/\1;/g; p' infile

It reads the whole file to a buffer and replace all go words and all blanks that precede it with a semicolon. It yields:

go
CREATE TABLE foobar
(
    f1    INT,
    f2    INT,
    f3    FLOAT,
        f4    VARCHAR(32) NOT NULL,
    f5    INT,
    f6    datetime,
        f7    smallint
);

GRANT UPDATE, INSERT, DELETE, SELECT ON foobar TO dbusr;
CREATE UNIQUE INDEX idxu_foobar ON foobar (f1, f2);

EDIT to add an explanation (see comments):

It's not as hard as it seems.

:a; $! { N; ba } is a loop that reads every line of input to a buffer.

[[:space:]] matches any whitespace character and [^[:space:]] negates it. So the substitution command replaces from last non-whitespace character until the word go. If there is only whitespace before the go word as in the first case, the substitution doesn't match and does replace nothing.

edited Nov 2, 2013 at 21:02

answered Nov 2, 2013 at 20:18

Birei

36.4k3 gold badges80 silver badges84 bronze badges

3 Comments

Homunculus Reticulli Over a year ago

+1 for the one liner - however, the expression looks very fearsome (even though it worked perfectly on my files) - I don't understand how it works. Could you please explain how the expressions work, so I can learn from your example?.

Birei Over a year ago

@HomunculusReticulli: Updated the answer with an explanation.

Homunculus Reticulli Over a year ago

Thanks for the explanation. I'll take the time to read it to understand it. Your answer is just as good as 1_CR's, but I'll go with his, since he answered first, and his solution uses sed. Thanks for your help though, much appreciated.

Collectives™ on Stack Overflow

sed replace text matching complicated regex pattern

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related