How to replace a string inside an SQL file with sed

Question

I have a Multi-site WordPress installation, that contains several sites for the company I work for, and I have produce a bash script, that extracting the database tables for each site separately.

In addition, we title each site based on the location of each copy. In example the local copy of a site it is named like Site Title - Local, the staging has a title like Site Title - Staging and the production like Site Title.

In my script I can use the following sed command in order to find and replace the - Local to - Staging

sed -i -e "s/\s\-\sLocal/\ \-\ Staging/g" ${staging_sql_file};

But the following doesn't work for removing the string - Local:

sed -i -e "s/ - Local//g" ${staging_sql_file};

I also have try patterns like the following ones:

"s/\s\-\sLocal//g"
"s/ \- Local//g"
"s/\s\-\sLocal/\s/g"
"s/ \- Local/ /g"

but none of these works.

Can somebody help me with that problem ?

UPDATE #1

When I try the regex's I display above, I get an error from sed.

UPDATE #2

My SQL files are like the following one:

LOCK TABLES `wp_16_options` WRITE;
/*!40000 ALTER TABLE `wp_16_options` DISABLE KEYS */;
INSERT INTO `wp_16_options` VALUES (1,'siteurl','https://www.site-url.com','yes'),(2,'home','https://www.site-url.com','yes'),(3,'blogname','Site Title - Local','yes'),(4,'blogdescription','Just another multi site wp site','yes'),(5,'users_can_register','0','yes'),...

anubhava · Accepted Answer · 2015-01-21 16:37:37Z

1

Try this sed:

sed -i.bak 's/[[:blank:]]*-[[:blank:]]*Local//g' "${staging_sql_file}"

i.e. use of POSIX class [[:blank:]] in place of a horizontal white-space.

answered Jan 21, 2015 at 16:37

anubhava

790k67 gold badges603 silver badges671 bronze badges

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

6 Comments

anubhava Over a year ago

Ok can you show me output of grep Local "${staging_sql_file}" | cat -vte command

KodeFor.Me Over a year ago

Unfortunately not, because the result it's huge.

anubhava Over a year ago

Oh that is to save original file with .bak extension in case something goes wrong in editing.

KodeFor.Me Over a year ago

Oh ok ! But I don't mind to backup the original file, as the original files is always a fresh copy of my local database.

anubhava Over a year ago

ok but that is unimportant here. Main thing is replacement should work for you and only reason it doesn't work if sql file has some invalid characters

|

Amit · Accepted Answer · 2015-01-21 16:37:34Z

1

[SO]$ cat file
Site Title - Local
Site Title - Staging
Site Title

[SO]$ sed 's/ - Local//g' file
Site Title
Site Title - Staging
Site Title

answered Jan 21, 2015 at 16:37

Amit

20.6k7 gold badges51 silver badges55 bronze badges

Collectives™ on Stack Overflow

How to replace a string inside an SQL file with sed

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related