1

Using bash shell on Ubuntu 14.04. Trying to do a simple search and replace on every line of a file. I have tried

perl -pi -e "s/.*/DELETE FROM my_object_times where ID = '$1';/g" ids.csv

but this leaves me with a file full of

DELETE FROM my_object_times where ID = '';

and everything I wanted to capture is not inserted at all. What is a better way to do this search and replace? I expected the final results to be something like

DELETE FROM my_object_times where ID = 'abcdef12341234abcdef';

2 Answers 2

1
perl -pi -e "s/.*/DELETE FROM my_object_times where ID = '$1';/g" ids.csv

Since the Perl snippet is in double-quotes, the shell will expand $1 to whatever its current value is (probably empty or unset in your case). You need to escape the dollar sign to prevent that. Also, you don't have a capture group in the pattern of the s/// operator, so $1 would not contain anything. (perl -w or use warnings would warn you about this.)

Either add parenthesis to the pattern, or use $&. Also, the global replacement doesn't seem to work well with a pattern that can be zero-width, so I'd suggest removing the g-flag.

So:

perl -w -pe "s/.*/DELETE FROM my_object_times where ID = '\$&';/" 

(Though & is not a valid variable in shell, so $& will be left as-is. But in general, the $ would need to be escaped.)

Usually, putting the Perl code in single-quotes would be better, as the dollar sign is quite common in Perl. But here the single quotes inside the Perl code make that a bit hard. One option is to present them in hex:

perl -w -pe 's/.*/DELETE FROM my_object_times where ID = \x27$&\x27;/' 
6
  • "the global replacement doesn't seem to work well with a pattern that can be zero-width" What exactly does this mean? Either it does (and I think it does) or does not work., surely?
    – JonBrave
    Commented Apr 11, 2017 at 18:28
  • 1
    @JonBrave, It gives me more than one match per line: one replacing the contents of the line, and one or two more, hitting zero-length parts around the end of the line (around the newline). This prints more than one x: echo "a" | perl -pe 's/.*/x/g;'. I didn't stay to find out what exactly happens.
    – ilkkachu
    Commented Apr 11, 2017 at 18:37
  • Wow! I thought the .* would gobble everything it could and would not match another time! Try also: echo "a" | perl -pe 's/.*/$&x/g;'
    – JonBrave
    Commented Apr 11, 2017 at 18:48
  • 1
    s/.+/x/g and s/^.*/x/g only match once, but s/.*$/x/g matches a couple of times, so yeah, it seems to think there's something at the end of the string it didn't look into yet, and since the pattern matches an empty string...
    – ilkkachu
    Commented Apr 11, 2017 at 18:52
  • 2
    @BowlOfRed, the single-quotes are in the replacement string, they should be there in the output, not quote anything. Otherwise that would be a good idea, though. The usual '...'\''x'\''...' doesn't look that nice to my eye either.
    – ilkkachu
    Commented Apr 11, 2017 at 19:03
0

This will do it:

sed -i "s/.*/DELETE FROM my_object_times where ID = '&';/" ids.csv

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .