CODEWITHSUNDEEP
linuxawktext-manipulation
RonnyKnoxville
RonnyKnoxville

Reputation: 6394

Manipulate selected variable using AWK

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?

Upvotes: 1

Views: 903

Answers (4)

RonnyKnoxville
RonnyKnoxville

Reputation: 6394

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.

Upvotes: 1

Nikhil Gupta
Nikhil Gupta

Reputation: 291

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

Upvotes: 0

Ed Morton
Ed Morton

Reputation: 203645

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`]/

Upvotes: 3

Nikhil Gupta
Nikhil Gupta

Reputation: 291

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

Upvotes: -1

Related Questions