CODEWITHSUNDEEP
regexbashunixsed
Georgina Skibinski
Georgina Skibinski

Reputation: 13387

Regex SQL syntax in bash

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.

Upvotes: 0

Views: 176

Answers (4)

Pedro Lobito
Pedro Lobito

Reputation: 98921

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.

Upvotes: 1

chepner
chepner

Reputation: 531245

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.

Upvotes: 1

builder-7000
builder-7000

Reputation: 7627

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

Upvotes: 0

Matias Barrios
Matias Barrios

Reputation: 5056

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!

Upvotes: 0

Related Questions