Using placeholders/variables in a sed command

Question

I want to store a specific part of a matched result as a variable to be used for replacement later. I would like to keep this in a one liner instead of finding the variable I need before hand.

when configuring apache, and use mod_rewrite, you can specificy specific parts of patterns to be used as variables,like this:

RewriteRule ^www.example.com/page/(.*)$ http://www.example.com/page.php?page=$1 [R=301,L]

the part of the pattern match that's contained inside the parenthesis is stored as $1 for use later. So if the url was www.example.com/page/home, it would be replaced with www.example.com/page.php?page=home. So the "home" part of the match was saved in $1 because it was the part of the pattern inside the parenthesis.

I want something like this functionality with a sed command, I need to automatically replace many strings in a SQL dump file, to add drop table if exist commands before each create table, but I need to know the table name to do this, so if the dump file contains something like:

...
CREATE TABLE `orders`
...

I need to run something like:

cat dump.sql | sed "s/CREATE TABLE `(.*)`/DROP TABLE IF EXISTS $1\N CREATE TABLE `$1`/g"

to get the result of:

...
DROP TABLE IF EXISTS `orders`
CREATE TABLE `orders`
...

I'm using the mod_rewrite syntax in the sed command as a logical example of what I'm trying to do.

Any suggestions?

Answer 1

sed '/CREATE TABLE \([^ ]*\)/ s//DROP TABLE IF EXISTS \1; &/'

Find a CREATE TABLE statement and capture the table name. Replace it with 'DROP TABLE IF EXISTS' and the table name, plus a semi-colon to terminate the statement, and a copy of what was matched to preserve the CREATE TABLE statement.

This is classic sed notation. Since you're using bash, there's a chance you're using GNU sed and will need to add --posix to use that notation, or you'll need to fettle the script to use GNU's non-standard sed regexes. I've also not attempted to insert a newline into the output. You can do that with GNU sed if it is important enough to you.

The key points are the parentheses (classically needing to be escaped with a backslash) are the capture mechanism, and backslash-number is the replacement mechanism.

Answer 2

 sed -r "s/CREATE TABLE (\`.*\`)/DROP TABLE IF EXISTS \1\n &/g" dump.sql

test:

kent$  cat t.txt

CREATE TABLE `orders`
...

CREATE TABLE `foo`
...
...

CREATE TABLE `bar`
...


kent$  sed -r "s/CREATE TABLE (\`.*\`)/DROP TABLE IF EXISTS \1\n &/g" t.txt

DROP TABLE IF EXISTS `orders`
 CREATE TABLE `orders`
...

DROP TABLE IF EXISTS `foo`
 CREATE TABLE `foo`
...
...

DROP TABLE IF EXISTS `bar`
 CREATE TABLE `bar`
...