How do I escape apostrophes within an awk statement?

Question

I'm using Ubuntu Linux with the bash shell. How do I escape apostrophes within an awk statement? I want to output some SQL, after extracting data from a file using awk, so I have:

awk -F',' '{print "SELECT * from user where id = '"$2"';"}' myfile.csv

But this prints out only:

SELECT * from user where id = ;

If I modify the above to

awk -F',' '{print "SELECT * from user where id = "$2";"}' myfile.csv

then I get a statement like

SELECT * from user where id = cf915247dfcf47b6814b5350e5cbdfd8;

but there are no single quotes around the id, which I need.

Answer 1

You might want to approach this in two ways:

1. Pass single quote via variable:

   awk -v sq="'" -F',' '{print "SELECT * from user where id = "sq$2sq";"}' myfile.csv
   

2. Make use of hex value of the single quote in printf statement:

   awk -F',' '{printf "SELECT * from user where id = %c%s%c;\n",0x27,$2,0x27}' myfile.csv
   

Answer 2

You can just use \47 (or \047) in your string, which awk takes to mean '.

awk -F, '{print "SELECT * from user where id = \47" $2 "\47;"}' myfile.csv

That's sort of inelegant ("What's the repeated magic value '47' doing?!") but the syntax is clean and this technique for making a single quote is pretty well known.

awk is what treats \47 specially, not your shell. In ' '-quoted strings, Bourne-style shells like Bash do not perform any expansions. C Shells like tcsh do, but they don't expand \47 and this still works.

You may prefer to use \047 instead of \47. They both work, unless \47 is followed immediately by an octal digit (0-7), in which case you must use \047 to avoid indicating the wrong character. Note that, unlike in some languages, the 47 in \47 is octal even without the leading 0; see below.

What does \47 mean? Why does this use \47 when that way uses 0x27?

This is because two sixteens make four eights.

• \47 is an octal character sequence, so it's 4×8 + 7×1 = 39.
• 0x27 is a hexadecimal numeric literal, so it's 2×16 + 7×1 = 39.
  Then printf's %c specifier formats it as a character.

Checking with python -c 'print int("47", 8), int("27", 16)' gives 39 39, as expected.

Answer 3

It is going to look crazy, but here you go:

awk -F',' '{print "SELECT * from user where id = '"'"'" $2 "'"'"'" ";"}' myfile.csv

SELECT * from user where id = 'cf915247dfcf47b6814b5350e5cbdfd8';

Hope this helps!