Using placeholders/variables in a sed command
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`
...
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.
This is called a "back reference". And sed will start numbering the things between the parenthesis. \(...\)
Note the use of backslash as an escape character above.
Ref: https://www.gnu.org/software/sed/manual/html_node/Regular-Expressions.html