4

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.

2
  • So you want to have cf915247dfcf47b6814b5350e5cbdfd8 single-quoted ? like 'cf915247dfcf47b6814b5350e5cbdfd8' ? – Sergiy Kolodyazhnyy Jan 23 '17 at 20:40
  • Yes you said it exactly right – Dave Jan 23 '17 at 20:41
2

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
    
1
  • I like that. Never thought about setting a single quote variable. +1 – Terrance Jan 23 '17 at 20:53
1

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!

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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