1

I'm trying to replace a string in database. I found the possible solutions below. Using # or % works to me, but not for / and |. Can anyone explain why the first two works and what the differences are between them please? 

find . -type f -name 'file.sql' -exec sed -i '' s#http://a.com#http://b.com#g {} +
find . -type f -name 'file.sql' -exec sed -i '' s%http://a.com%http://b.com%g {} +
find . -type f -name 'file.sql' -exec sed -i '' s/http://a.com/http://b.com/g {} +
find . -type f -name 'file.sql' -exec sed -i '' s|http://a.com|http://b.com|g {} +
Quasímodo
  • 18,625
  • 3
  • 35
  • 72
Stickers
  • 123
  • 6
  • The way you wrote it, all of them are syntax errors. :) If you add single quotes around `s...g`, then only the second is a syntax error. Neither problem has anything to do with `find`. – Satō Katsura Jun 04 '17 at 15:04
  • See [leaning toothpick syndrome](https://en.wikipedia.org/wiki/Leaning_toothpick_syndrome). – Satō Katsura Jun 04 '17 at 15:19
  • @SatoKatsura Now I know the issue is mainly caused by regex escaping, if I quote everything like `'s|'"http://a.com"'|'"http://b.com"'|g'` any of the line above will work, but the question still remains open, what are the difference between `#`, `%`, `/` and `|`? – Stickers Jun 04 '17 at 16:10
  • @Pangloss, you added the `%` version later, didn't it work? – ilkkachu Jun 04 '17 at 17:18
  • 2
    (1) Your problem has nothing to do with regex escaping. It has to do with you using shell special characters without escaping them. You can solve that by enclosing `s|...|g` etc. in single quotes. You don't need double quotes for the URLs. (2) `s/.../g` has the leaning toothpick syndrome, because the delimiter `/` for `s` is also found in `http://` and in the paths separators. (3) There is no difference in using `#`, `%`, or `|` as delimiters for `sed`, they're just alternative solutions to the leaning toothpick problem. Any other "spare" character would do. (4) `man sed`, `man bash`. – Satō Katsura Jun 04 '17 at 20:59
  • @ilkkachu Yes, both `#` and `%` worked without any issues. – Stickers Jun 04 '17 at 21:37
  • @SatoKatsura Thanks so much the explanation, it helped a lot. – Stickers Jun 04 '17 at 23:23

1 Answers1

4

Sed's substitution command typically uses / as the delimiter between the s command, the search string, the replacement string, and any flags. If, however, either of the search string or replacement string may contain a /, then people will change the delimiter to a character that is not in either of the strings.

You received the other error from sed when you told it to use an empty string as the backup extension; since you're not using an extension, don't put the empty quotes in. (Note: This only applies to GNU sed. If you're using BSD sed, e.g. on a Mac, then you do need the empty string to specify no backup extension.)

Sed does not care what the delimiter is, but your surrounding shell might.

The | symbol introduces a pipe. To use it as a delimiter, you would have to quote it in some fashion to protect them from the shell.

The # symbol may introduce a comment string, based on your shell (bash only begins a comment if the # is the first character of a word), so better to be safe and quote it.

Since your data contains / characters, you either need to use a different delimiter, or escape every instance of / in the search and replacement strings. This leads to what's known as leaning toothpick syndrome because of the hard-to-read appearance of the \/.

Thus, you'd need something like:

find . -type f -name 'file.sql' -exec sed -i -e 's#http://a.com#http://b.com#g' {} +

or

find . -type f -name 'file.sql' -exec sed -i -e 's|http://a.com|http://b.com|g' {} +

or

find . -type f -name 'file.sql' -exec sed -i -e "s|http://a.com|http://b.com|g" {} +

or

find . -type f -name 'file.sql' -exec sed -i -e 's/http:\/\/a.com/http:\/\/b.com/g' {} +
Wildcard
  • 35,316
  • 26
  • 130
  • 258
Jeff Schaller
  • 66,199
  • 35
  • 114
  • 250
  • @JeffSchaller I tried the lines you suggested, and all gets errors, it seems you removed `''` after `-i`, any ideas? – Stickers Jun 04 '17 at 15:32
  • 1
    My 2cents, it's also good to have an explicit `-e` lest the sed command be taken for the backup file extension. Plus makes for an unambiguous code. –  Jun 04 '17 at 16:35
  • @JeffSchaller Thank you, I think I get it now, so it doesn't matter for which delimiter is being used, it works as long as there isn't anything the same as that delimiter in string. Then what if the string I want to replace is unknown means it could possibility contain some or all of them, e.g in .sh with variables. – Stickers Jun 04 '17 at 22:04
  • See: https://unix.stackexchange.com/q/129059/117549 – Jeff Schaller Jun 04 '17 at 22:53
  • And: https://unix.stackexchange.com/q/32907/117549 – Jeff Schaller Jun 04 '17 at 22:54