I try to create a short perl command that creates SQL inserts for my DB based on a text file. However, I am not able to get in the single-quotes used by SQL
perl -pe '$i += 1; chomp; @a = split /\t/; $_ = "INSERT INTO XYZ VALUES($i, \'$a[4]\');\n"'
results in a syntax error near unexpected token `)'
Any ideas?
perl -pe '$i += 1; chomp; @a = split /\t/; $_ = "INSERT INTO XYZ VALUES($i, \047$a[4]\047);\n";'
You need to escape them for the shell, not for Perl. This requires a slightly different syntax. Assuming you're running this under bash, ksh, or similar, then
perl -e 'print "greengrocer'\''s\n"'
should print greengrocer's.
Alternatively, you could insert the character as a hex escape sequence:
perl -e 'print "greengrocer\x27s\n"'
perl -pe "\$i += 1; chomp; @a = split /\t/; \$_ = \"INSERT INTO XYZ VALUES(\$i, '\$a[4]');\n\""
From this page it states for BASH: "A single quote may not occur between single quotes, even when preceded by a backslash.". So use double quotes instead and escape as necessary.
Use the technique @Porculus suggested in his answer:
perl -pe '$i += 1; chomp; @a = split /\t/; $_ = "INSERT INTO XYZ VALUES($i, '\''$a[4]'\'');\n";'
This closes the single-quoted string just before the single quote, then uses an escaped single-quote, then opens a new single-quoted string.
The beauty of this technique is that you can automate it:
sed -e "s/'/'\\\\''/g" -e "s/^/'/" -e "s/$/'/" <<'EOF'
$i += 1; chomp; @a = split /\t/; $_ = "INSERT INTO XYZ VALUES($i, '$a[4]');\n";
EOF
resulting a properly-quoted string you can paste on to the command line :
'$i += 1; chomp; @a = split /\t/; $_ = "INSERT INTO XYZ VALUES($i, '\''$a[4]'\'');\n";'
Or you can make a shell-script for it:
$ cat > quotify
#!/bin/sh
sed -e "s/'/'\\\\''/g" -e "s/^/'/" -e "s/$/'/"
^D
$ chmod +x quotify
$ ./quotify
$i += 1; chomp; @a = split /\t/; $_ = "INSERT INTO XYZ VALUES($i, '$a[4]');\n";
^D
'$i += 1; chomp; @a = split /\t/; $_ = "INSERT INTO XYZ VALUES($i, '\''$a[4]'\'');\n";'
(The above sed firstly replaces each ' with '\'' then puts a ' at the front and back.
Found a work-around by using a | for all single-quote and then in a second perl call replacing this | by a single quote (perl -pe "s/\|/'/g")
perl -pe '$i += 1; chomp; @a = split /\t/; $_ = "INSERT INTO XYZ VALUES($i, |$a[4]|, |$a[5]|, |$a[7]|, |$a[0]|, |$a[1]|, |$a[3]|);\n"' | perl -pe "s/\|/'/g"
Still interested in a better solution.
