AWK Field Separators Vanishing

Question

I am trying to use AWK to modify a long SQL script after removing a column from the database.

The lines I am trying to modify:

INSERT INTO `table` (`one`, `two`, `three`, `four`, `five`, `six`) VALUES ('alpha', 'bravo', 'charlie', 'delta', 'echo', 'foxtrot');

My AWK script:

BEGIN { 
    FS=","
} 
/`table`/ { $3=$8=""; print }

Which I run with:

awk -f script.awk in.sql > out.sql

This does ALMOST what I want it to do, except it seems to remove all of the Field Separators(commas):

INSERT INTO `table` (`one`  `two`   `four`  `five`  `six`) VALUES ('alpha'  'bravo'   'delta'  'echo'  'foxtrot');

What I would have expected to see is a double comma, that I would need to replace with a single comma using gsub or something.

What is happening to the commas?

Answer 1

You can set the output field separator, OFS, to a comma, to better preserve the input syntax.

Answer 2

When you assign a value to a field, awk reconstructs the input record using the Output Field Separator, OFS. You have 2 choices:

1. Set FS=OFS="," instead of just FS="," so the record gets rebuilt using commas, or
2. Do not assign a value to a field, but instead use REs to operate on the whole record.

You already know how to do "1" and that you'd then need to strip double OFSs to remove empty fields, here's how to do "2" using GNU awk for gensub():

$ awk '{print gensub(/(([^,]+,){2})[^,]+,(([^,]+,){4})[^,]+,/,"\\1\\3","")}' file
INSERT INTO `table` (`one`, `two`, `four`, `five`, `six`) VALUES ('alpha', 'bravo', 'delta', 'echo', 'foxtrot');

Note that since you aren't doing anything with individual fields, you don't need to specify FS or OFS and the record doesn't get recompiled so you don't need to remove resultant empty fields which can often be error-prone

You can do the same thing with sed if you prefer.