Ask Ubuntu is a question and answer site for Ubuntu users and developers. It only takes a minute to sign up.
Sign up to join this community
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.
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:
\2 is intentionally not used, because we replace the user name)'@ 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:
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
