1

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?

Improve this question
2
  • 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. Commented Sep 22, 2014 at 12:52
  • You're quite right, I'll edit the answer and question now Commented Sep 22, 2014 at 13:06

4 Answers 4

Reset to default
3

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`]/
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

7 Comments

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.
Thanks, I'll give \047 a go instead
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.
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
@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.
|
0

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
Improve this answer

Comments

-1

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
Improve this answer

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.