sed command replace text with ' character

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.

2 Answers

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.

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: