Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
1  
Great answer. Thank you. –  brpaz Jul 2 '13 at 15:24
    
You're welcome! –  lgarzo Jul 2 '13 at 15:29

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:

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

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

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.