re = /^(.*)\(([0-9]*),'([^']+)','([^']+)','([^']+)','([^']+)','([A-Z]+)','([0-9]{5})'/m
str = 'CALL insert_wpsl_store(34,\'American Health\',\'American Health Foods #2\',\'21202 State Highway 249 Ste. 500\',\'Houston\',\'TX\',\'77070\',\'USA\',29.997590,-95.578641,6);
CALL insert_wpsl_store(805,\'Granary Whole Foods\',\'1738 Kingsley Ave\',\'Orange Park\',\'FL\',\'32073\',\'USA\',30.164472,-81.733892,6);
CALL insert_wpsl_store(8056,\'Chamberlins\',\'4924 E. Colonial Dr.\',\'Orlando\',\'FL\',\'32803\',\'USA\',28.553286,-81.326066,8);
CALL insert_wpsl_store(1642,\'Kampai\',\'Roble 635\',\'Col. Valle del Campestre\',\'San Pedro Garza García\',\'NL\',\'66266\',\'MX\',25.656827,-100.372697,6);
CALL insert_wpsl_store(1643,\'Sr. Tanaka\',\'Calz San Pedro Sur # 102\',\'Col. Del Valle\',\'San Pedro Garza García\',\'NL\',\'64650\',\'MX\',25.656655,-100.371225,6);
CALL insert_wpsl_store(807,\'Chamberlins\',\'1170 Oviedo Mall Blvd.\',\'Oviedo\',\'FL\',\'32765\',\'USA\',28.661852,-81.239646,8);
CALL insert_wpsl_store(808,\'Chamberlins\',\'7600 Dr. Phillips Blvd.\',\'Orlando\',\'FL\',\'32819\',\'USA\',28.451281,-81.489865,8);
CALL insert_wpsl_store(809,\'Chamberlins\',\'1086 Montgomery Road\',\'Altamonte Springs\',\'FL\',\'32714\',\'USA\',28.686480,-81.404065,8);
CALL insert_wpsl_store(810,\'Chamberlins\',\'1531 US Hwy 98 S\',\'Lakeland\',\'FL\',\'33803\',\'USA\',28.024486,-81.925599,8);
CALL insert_wpsl_store(811,\'Chamberlins\',\'1114 N. John Young Parkway\',\'Kissimmee\',\'FL\',\'34741\',\'USA\',28.301877,-81.416101,8);
CALL insert_wpsl_store(812,\'Lee’s Marketplace\',\'Lee’s Smithfield 10810\',\'850 South Main\',\'Smithfield\',\'UT\',\'84335\',\'USA\',41.818762,-111.831481,6);
CALL insert_wpsl_store(8133,\'Lee’s Marketplace\',\'Lee’s Mkt 10812\',\'725 North Redwood Rd\',\'North Salt Lake\',\'UT\',\'84054\',\'USA\',40.854739,-111.930919,6);
CALL insert_wpsl_store(814,\'Lee’s Marketplace\',\'Lee’s Logan 10805\',\'555 East 1400 N\',\'Logan\',\'UT\',\'84341\',\'USA\',41.757425,-111.820370,6);
CALL insert_wpsl_store(815,\'Lee’s Marketplace\',\'Lee’s N Ogden 10116\',\'2645 North Washington Blvd\',\'Ogden\',\'UT\',\'84414\',\'USA\',41.306876,-111.968701,6);
CALL insert_wpsl_store(816,\'Act Natural Health Foods\',\'695 Kings Bay Rd\',\'St Marys\',\'GA\',\'31558\',\'USA\',30.783249,-81.613175,6);
CALL insert_wpsl_store(817,\'Herbs & More\',\'127 Newnan Rd\',\'Carrollton\',\'GA\',\'30117\',\'USA\',33.579385,-85.065897,6);
CALL insert_wpsl_store(818,\'Sevananda Nat Foods\',\'467 Moreland Ave NE\',\'Atlanta\',\'GA\',\'30307\',\'USA\',33.767031,-84.348501,6);
CALL insert_wpsl_store(819,\'Don Quijote\',\'801 Kaheka St.\',\'Honolulu\',\'HO\',\'96814\',\'USA\',21.293706,-157.838691,6);
CALL insert_wpsl_store(820,\'Quillins Foods\',\'808 South Main St\',\'Monona\',\'IA\',\'52159\',\'USA\',43.043150,-91.390369,6);
CALL insert_wpsl_store(821,\'Campbell\\\'S\',\'2749 100th St.\',\'Urbandale\',\'IA\',\'50322\',\'USA\',41.619941,-93.755680,7);'
subst = '$1($2,\'$3 - $4\',\'$5\',\'$6\',\'$7\',\'$8\''
result = str.gsub(re, subst)
# Print the result of the substitution
puts result
Please keep in mind that these code samples are automatically generated and are not guaranteed to work. If you find any syntax errors, feel free to submit a bug report. For a full regex reference for Ruby, please visit: http://ruby-doc.org/core-2.2.0/Regexp.html