Regex SQL syntax in bash

Question

My problem is the following- I have file with SQL syntax in it. For example content of sql.txt:

select a,b from
x.tabl where A;

select b,c from x.tabl
;
select c,d from x.tabl where B;
Select d from
tabl
;
select z from x.table;
Select a,b from x.tabl_3;

Now what I want to do is to rewrite this file in bash, replacing all x.tabl and tabl with y.rtabl (new name differs only by name prefix and db).

So expected output is:

select a,b from
y.rtabl where A;

select b,c from y.rtabl
;
select c,d from y.rtabl where B;
Select d from
y.rtabl
;
select z from x.table;
Select a,b from x.tabl_3;

what I've got so far:

eval "sed -e 's/x.tabl/y.rtabl/' sql.txt>copysql.txt"
eval "mv copysql.txt sql.txt"
eval "sed -e 's/tabl/y.rtabl/' sql.txt>copysql.txt"
eval "mv copysql.txt sql.txt"

Or

eval "sed -e 's/\Wx.tabl\W/ y.rtabl /' sql.txt>copysql.txt"
eval "mv copysql.txt sql.txt"
eval "sed -e 's/\Wtabl\W/ y.rtabl /' sql.txt>copysql.txt"
eval "mv copysql.txt sql.txt"

The mv part works quite neatly for me, but sed requires some serious tuning.

sed -i is an inexplicable, non-standard extension to do what ed already does. — chepner
– chepner, Commented Nov 15, 2019 at 17:46

chepner · Accepted Answer · 2019-11-15 18:14:40Z

1

You don't need eval here. Just use

sed -E 's/(^|[[:space:]]+)(x\.)?tabl(;|$|[[:space:]]+)/\1y.rtabl\3\4/g' \
  sql.txt > copysql.txt && mv copysql.txt sql.txt

(^|[[:space:]]+) matches either the beginning of the line or arbitrary whitespace preceding the table name to match
(x\.)?tabl matches tabl preceded by an optional x_.
(;|$|[[:space:]]+) matches either the end of a statement, the end of a line or arbitrary whitespace.

Together, these three regular expressions will match tabl or x_tabl and any whitespace wrapped around it, and replace it with y.rtabl and whatever surrounding whitespace was captured.

Note that this uses POSIX extended regular expressions, so will work with any POSIX-compliant version of sed. Some implementations (notably GNU sed) may allow substantially shorter solutions, especially where they support matching a word boundary.

edited Nov 15, 2019 at 18:14

answered Nov 15, 2019 at 17:45

chepner

538k77 gold badges594 silver badges746 bronze badges

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

6 Comments

Georgina Skibinski Over a year ago

That solves half of the problem- I don't want to replace 2 last sql-s (so don't touch x.table and x.tabl_3).

chepner Over a year ago

Yeah, I'm trying to find a way to address that without requiring extensions to standard sed/ed. Do you know what version of sed you are using, if you can rely on that?

chepner Over a year ago

Updated with a working version that only assumes POSIX extended regular expressions (which eliminates ed as an option).

chepner Over a year ago

Updated to handle the case where the table occurs immediately before a ;, something not appearing in the sample input.

chepner Over a year ago

Keep in mind, though, this makes some pretty significant assumptions about what is in the file. Something like select c, d from x.tabl where foo = "x.tabl;" will be mishandled. You simply can't parse a non-regular language with regular expressions.

|

Pedro Lobito · Accepted Answer · 2019-11-15 20:16:39Z

1

Here's a Python solution:

sql_replace.py

import re
with open("sql.txt") as f:
    sql = f.read()

sql = re.sub(r"\bx\.tabl\b", "y.rtabl", sql)
sql = re.sub(r"\btabl\b", "y.rtabl", sql)

with open("sql_new.txt", "w") as f:
    f.write(sql)

Python Demo

Run python sql_replace.py on the same dir as sql.txt and a new file named sql_new.txt will be created containing:

select a,b from
y.rtabl where A;

select b,c from y.rtabl
;
select c,d from y.rtabl where B;
Select d from
y.rtabl
;
select z from x.table;
Select a,b from x.tabl_3;

Note:

The regexes are pretty simple, but \b (word boundaries) is a must, so x.table and x.tabl_3 don't get replaced.

edited Nov 15, 2019 at 20:16

answered Nov 15, 2019 at 19:08

Pedro Lobito

99.8k36 gold badges274 silver badges278 bronze badges

2 Comments

Georgina Skibinski Over a year ago

I was going to write, that python also would do :) thanks, this works!

Pedro Lobito Over a year ago

You're welcome. I choose python for the simplicity of the regex construction. Glad it worked!

Matias Barrios · Accepted Answer · 2019-11-15 17:49:40Z

0

Is this good enough :

sed -E "s/(x[.]tabl($| )|tabl($| ))/NEWTABLE /g" input.txt

Output

select a,b from
NEWTABLE where A;

select b,c from NEWTABLE 
;
select c,d from NEWTABLE where B;
Select d from
NEWTABLE 
;
select z from x.table;
Select a,b from x.tabl_3;

Regards!

edited Nov 15, 2019 at 17:49

answered Nov 15, 2019 at 17:44

Matias Barrios

5,0864 gold badges31 silver badges71 bronze badges

2 Comments

Georgina Skibinski Over a year ago

I don't want to replace table names in 2 last sql-s. It's different table.

Georgina Skibinski Over a year ago

Hm, it doesn't capture 3rd sql from the bottom- I don't have white space after tabl

builder-7000 · Accepted Answer · 2019-11-15 18:10:45Z

0

To overwrite sql.txt in-place do:

sed -i'' 's/\(x.\)\?tabl\>/y.rtabl/g' sql.txt

Changes to OP sed command:

Remove eval
escape dots \. in pattern
do global replacement
use tabl\> instead of tabl for exact match

edited Nov 15, 2019 at 18:10

answered Nov 15, 2019 at 17:46

builder-7000

7,7183 gold badges26 silver badges45 bronze badges

2 Comments

Georgina Skibinski Over a year ago

Thanks, it solves part of the problem. The other part is not to touch 2 last sql-s i.e. x.tabl_3 and x.table.

Georgina Skibinski Over a year ago

I don't want to replace neither tabl_3 nor table, only tabl and x.tabl

Collectives™ on Stack Overflow

Regex SQL syntax in bash

4 Answers 4

6 Comments

2 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related