regex - Using perl, how to convert sql block in logs to proper sql? -

i have hugh log file sql debugs. want change them proper sql.

for example in logs:

**** debug      fri aug 09 13:05:13 pdt 2013    1376078713845   /atg/web/viewmapping/viewmappingrepository      executeupdate2: [++sqlinsert++] **** debug      fri aug 09 13:05:13 pdt 2013    1376078713845   /atg/web/viewmapping/viewmappingrepository       insert vmap_pv2pvad_rel(view_id,name,attr_id) **** debug      fri aug 09 13:05:13 pdt 2013    1376078713845   /atg/web/viewmapping/viewmappingrepository       values(?,?,?) **** debug      fri aug 09 13:05:13 pdt 2013    1376078713845   /atg/web/viewmapping/viewmappingrepository      -- parameters -- **** debug      fri aug 09 13:05:13 pdt 2013    1376078713845   /atg/web/viewmapping/viewmappingrepository      p[1] = {pd} ampvpuroleallmembers (java.lang.string) **** debug      fri aug 09 13:05:13 pdt 2013    1376078713845   /atg/web/viewmapping/viewmappingrepository      p[2] = {pd} synthcategorydefinition (java.lang.string) **** debug      fri aug 09 13:05:13 pdt 2013    1376078713845   /atg/web/viewmapping/viewmappingrepository      p[3] = {pd: attributes} ampvattpurolesmemberscatdef (atg.adapter.gsa.singlevaluegsaid) **** debug      fri aug 09 13:05:13 pdt 2013    1376078713845   /atg/web/viewmapping/viewmappingrepository      [--sqlinsert--] 

with below perl code

#!/usr/bin/perl use strict; use warnings;  open(file1, 'dps_ui_preview_view.log'); while (<file1>) {    if (/\+\+sql[insert,update]/../\-\-sql[insert,update]/) {     $_ =~ s/^.*\t//g;     print $_;    } }  close(file1); 

i able grab below sql format

[++sqlupdate++]  update vmap_im     set item_path=?   id=? -- parameters -- p[1] = {pd: itempath} /atg/userprofiling/externalprofilerepository (java.lang.string) p[2] = {pd} amimeuusers (java.lang.string) [--sqlupdate--]  [++sqlinsert++]  insert vmap_fh(id,name,component_path)  values(?,?,?) -- parameters -- p[1] = {pd} amfhpufh (java.lang.string) p[2] = {pd: name} external preview users formhandler (java.lang.string) p[3] = {pd: path} /atg/remote/userprofiling/assetmanager/editor/service/previewuserassetservice (java.lang.string) [--sqlinsert--] 

i need change above statements below

update vmap_im set item_path='/atg/userprofiling/externalprofilerepository' id='amimeuusers'; insert vmap_fh(id,name,component_path) values('amfhpufh','external preview users formhandler','/atg/remote/userprofiling/assetmanager/editor/service/previewuserassetservice') 

can please let me know how can achieve that? guide lines appreciated.

with of regex grouping mentioned user2676655 here full code

#!/usr/bin/perl use strict; use warnings;  $log; $match = 0; $q=""; $p=""; open(file1, 'dps_ui_preview_view.log'); while (<file1>) {    if (/\+\+sql[insert,update]/../\-\-sql[insert,update]/) {       $_ =~ s/^.*\t//g;       $log = $log . $_;       if($_ =~ m/\-\-\]/) {           ($q,$p) = ($log =~ /\+\+\](.*)-- parameters --(.*)\[--/s);           $p =~ s/^\s+//;           @params = split(/\n/,$p);           foreach $i (@params) {             ($val) = ($i =~ /\}(.*)\(/);              $val =~ s/^\s+//;             $val =~ s/\s+$//;             $q =~ s/\?/'$val'/;           }           $q =~ s/\n/ /g;           $q =~ s/\s+/ /g;           $q =~ s/ $/;/g;           print $q,"\n";           $log="";        }    } }  close(file1); 

try this:

$log = "[++sqlupdate++] update vmap_im set item_path=? id=? -- parameters -- p[1] = {pd: itempath} /atg/userprofiling/externalprofilerepository (java.lang.string) p[2] = {pd} amimeuusers (java.lang.string) [--sqlupdate--]";   ($q,$p) = ($log =~/\+\+\](.*)-- parameters --(.*)\[--/s);  $p =~ s/^\s+//; @params = split(/\n/, $p);  foreach (@params) {   ($val) = ($_ =~/\}(.*)\(/) ;   $val =~ s/^\s+//;   $val =~ s/\s+$//;    $q =~s/\?/'$val'/; }  $q =~s/\n/ /g; $q =~s/\s+/ /g; print $q;