sed command replace text with ' character

Question

I have an very big sql file that I cant open in a gui editor. I need to replace the string 'user1'@'localhost' (note the ` character) by 'user2'@'localhost' but i am having troubles finding the right syntax for sed command.

I can get the strings to replace correcly using the following grep command:

grep -w 'user1`@`localhost'

Any help please? Thank you.

Answer 1

I thought of a more general solution than as your question asks for.

Let's imagine you need to find a string based on your regular expression search criteria. You'd like to replace only a part of it, and leave the other matching parts unchanged.

To demonstrate with an example:

echo "'some-name'@'some-host'" | sed -r "s/(')([^']+)('@'[^']*')/\1user2\3/g"

will display:

'user2'@'some-host'

The sed command performs the replacement by using the s/search-regexp/replacement/g syntax. In our case:

• (') matches the first single quote. This is trivial, but could be more complex. It represents the part of the string before the replacement. sed assigns the value of this sub-expression to the special variable \1.
• ([^']+) matches the user name. Basically any character starting from the previous position that is not a quote. sed assigns the value of this sub-expression to the special variable \2.
• ('@'[^']*') matches the '@'host-name' part. Similarly to the previous sub-expression, a quote, a @, a quote again and any character that is not a quote and then a quote at the end. sed assigns the value of this sub-expression to the special variable \3.

The replacement part will replace anything that matched the search-regexp. By using the variables shown above, we can replace the user name and leave the other regions intact. \1 + your new user name + \3 will produce the desired result. Thus:

\1user2\3

Results in:

• Whatever contents the first sub-expression has, (it is a single quote)
• followed by the string "user2", (note \2 is intentionally not used, because we replace the user name)
• and finally the contents of the 3rd sub-expression (which is a '@ and the host name in quotes).

If you cat your script file and pipe it to the sed command, you should get the desired result.

Answer 2

Wrapping your sed command around speechmarks will work:

adder@adamj-T1500:~$ cat sed_test 
user1'@'localhost
adder@adamj-T1500:~$ sed -i -e "s/user1'@'localhost/user2'@'localhost/" sed_test
adder@adamj-T1500:~$ cat sed_test 
user2'@'localhost

There's a number of different ways you can escape a single quote for a sed command, there's a number of questions with answers on stackoverflow:

https://stackoverflow.com/questions/9112157/escaping-single-quotes

https://stackoverflow.com/questions/7990750/escape-single-quote

https://stackoverflow.com/questions/7517632/how-do-i-escape-double-and-single-quotes-in-sed-bash