Manipulate selected variable using AWK

Question

I have an SQL file I am trying to manipulate using AWK. I have the following line which splits my SQL file on VALUES as I would like to handle the text before and after this field differently.

The original file looks something like this:

INSERT INTO `drt_mig_user`.`parametric_object`(`id`, `active`, `priority`, `createdatetime`, `lastupdatedatetime`, `discriminator`) VALUES ('10085', '1', NULL, '2014-09-19 16:18:39', '2014-09-19 16:18:39', 'gate')

My AWK code:

cat file.txt | awk -F'VALUES' '{printf("$this->addSql(\"%sVALUES%s\");\n", $1, $2)}'

Which produces this:

$this->addSql("INSERT INTO `drt_mig_user`.`parametric_object`(`id`, `active`, `priority`, `createdatetime`, `lastupdatedatetime`, `discriminator`) VALUES ('10085', '1', NULL, '2014-09-19 16:18:39', '2014-09-19 16:18:39', 'gate') ");

Now all I need to do is remove drt_mig_user and remove the backticks from around the entire of the first variable $1 so that it looks something like this:

$this->addSql("INSERT INTO parametric_object(id, active, priority, createdatetime, lastupdatedatetime, discriminator) VALUES ('10085', '1', NULL, '2014-09-19 16:18:39', '2014-09-19 16:18:39', 'gate') ");

Is there a way to manipulate the variables in separate ways like this?

You don't HAVE single quotes (`'`) within your first variable, you have backticks (`\``). You did say though that your actual input `looks something like this` so is this one way that your posted sample input is actually NOT what your real input looks like? Please post sample input that is exactly the same as your real input in all important respects. — Ed Morton, Sep 22 '14 at 12:52

score 3 · Answer 1 · answered Sep 22 '14 at 13:19

To get the output you want from the input you posted is just:

$ awk -F'VALUES' '{gsub(/drt_mig_user`\.|`/,"",$1); printf("$this->addSql(\"%sVALUES%s\");\n", $1, $2);}' file
$this->addSql("INSERT INTO parametric_object(id, active, priority, createdatetime, lastupdatedatetime, discriminator) VALUES ('10085', '1', NULL, '2014-09-19 16:18:39', '2014-09-19 16:18:39', 'gate')");

If you DO have single quotes in $1, just change the gsub regexp to

/drt_mig_user`\.|[\047`]/

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

You can gsub the results before they go to print. The answer is:

awk -F'VALUES' '{gsub(/`/,"",$1); gsub("drt_mig_user.", "", $1); printf("$this->addSql(\"%sVALUES%s\");\n", $1, $2);}'

Which produces the desired:

$this->addSql("INSERT INTO parametric_object(id, active, priority, createdatetime, lastupdatedatetime, discriminator) VALUES ('10085', '1', NULL, '2014-09-19 16:18:39', '2014-09-19 16:18:39', 'gate') ");

Originally I was trying to escape single quotes rather than back-ticks as I was confusing the two, anyone looking for an answer to that should check out this SO answer.

edited May 23 '17 at 11:57

Community

1
1

answered Sep 22 '14 at 10:45

RonnyKnoxville

6,166
10
46
75

1

Ignore that SO answer. The simple way to include single quotes in an awk script is to represent them with `\047`, e.g. `awk 'BEGIN{print "there\047s one"}'` instead of jumping back and forth between awk and shell with `awk 'BEGIN{print "there'"'"'s one"}'`. Both of those will print `there's one`. Even simpler, of course, is to put your awk script in a file and use literal single quotes where you need them and then execute it with `awk -f script ...` since it's just the fact that you're delimiting your script with single quotes that's causing the problem in the first place. – Ed Morton Sep 22 '14 at 12:38
Thanks, I'll give \047 a go instead – RonnyKnoxville Sep 22 '14 at 12:52
As I mentioned in a comment below your question, though, your posted sample input is using backticks instead of single quotes so how to handle single quotes is actually irrelevant anyway. Separately: you should delete this answer and edit your question to contain any relevant information instead. – Ed Morton Sep 22 '14 at 12:54
I've changed the wording of the question and answer, although I've left the sample output (as it was pasted) and the answer as it does actually work like that – RonnyKnoxville Sep 22 '14 at 13:08
code that produces the expected output for some sample input set is the starting point for a solution to a problem. In this case the `'"'"'` is doing absolutely nothing but obfusacting the code and you're using string instead of regexp delimiters in your gsub()s which will make enhancements needlessly complicated as any escape chars will need to be double-escaped. – Ed Morton Sep 22 '14 at 13:13
@EdMorton I usually create a variable for single quote and use it freely inside the `awk` script. `awk -v q="'" 'BEGIN{print "there" q "s one"}'` – jaypal singh Sep 22 '14 at 17:18
1

@jaypal Understood and when not in a regep context that's not a big deal since you're working with strings anyway, but then you're forced to use string delimiters instead of regexp delimiters to do something like `$0 ~ "there" q "s one"` instead of `/there\047s one/` then it becomes an issue as you then have to account for the double-parsing that strings go through so if the rest of the text contains escape chars you have to double-escape them, and if that's being read from a file or populated from a variable it gets messy very quickly. I find it simplest to just consistently use `\047`. – Ed Morton Sep 22 '14 at 18:35

score 0 · Answer 3 · answered Sep 23 '14 at 06:43

Above scripts will work fine for the given situation.

If you want a standard script which should work in all situation then you can use below script. It will not replace all dots(.) in $1

awk -F'VALUES' '{gsub(/`|drt_mig_user../,"",$1); printf("$this->addSql(\"%sVALUES%s\");\n", $1, $2);}' file.txt

score -1 · Answer 4 · answered Sep 22 '14 at 12:37

If you want then you can also use a simple loop for this which will

while read query do
    first_part_temp=$( echo ${query} | awk -F 'VALUES' '{print $1}')
    second_part=$(echo ${query} | awk -F 'VALUES' '{print $2}' file.txt)
    first_part=$(echo $first_part_temp | sed s/\`//g | sed s/drt_mig_user.//g)
    echo "\$this->addSql(\"${first_part} VALUES ${second_part} \");" >> output.txt
done < file.txt

Or if you want to use single line then you can use:

awk -F'VALUES' '{gsub(/`|drt_mig_user|\./,"",$1); printf("$this->addSql(\"%sVALUES%s\");\n", $1, $2);}' file.txt

The above shell script is very buggy (incorrect use of read, no setting of IFS, unquoted variables, etc.), inefficient, and just completely the wrong approach anyway. The awk script isn't bad but would remove every `.` in `$1` instead of just the one after drt_mig_user, fine for this specific 1 line of input but probably undesirable in general. — Ed Morton, Sep 22 '14 at 13:26

Manipulate selected variable using AWK

4 Answers4