使用regex和UltraEdit多行查找和替换

Question

我需要使用REGEX进行智能解析。我的问题是查找并替换具有插入查询的41k行文件。

这个文件中的一组线段是

INSERT INTO `cities_extended` VALUES ('Holtsville', 'NY', '00501', '40.8152', '-73.0455', 'Suffolk');
INSERT INTO `cities_extended` VALUES ('Holtsville', 'NY', '00544', '40.8152', '-73.0455', 'Suffolk');
INSERT INTO `cities_extended` VALUES ('Adjuntas', 'PR', '00601', '18.1788', '-66.7516', 'Adjuntas');
INSERT INTO `cities_extended` VALUES ('Aguada', 'PR', '00602', '18.381389', '-67.188611', 'Aguada');
INSERT INTO `cities_extended` VALUES ('Aguadilla', 'PR', '00603', '18.4554', '-67.1308', 'Aguadilla');
INSERT INTO `cities_extended` VALUES ('Aguadilla', 'PR', '00604', '18.4812', '-67.1467', 'Aguadilla');
INSERT INTO `cities_extended` VALUES ('Aguadilla', 'PR', '00605', '18.429444', '-67.154444', 'Aguadilla');

我想将每个INSERT INTOcities_extendedVALUES ('%1', '%2',替换为SELECT C.id FROM CITY C WHERE C.name = '' and C.state = ''，这样我的整个文件就会像这样

INSERT INTO `cities_extended` VALUES (SELECT C.id FROM CITY C WHERE C.name = 'Holtsville' and C.state = 'NY', '00501', '40.8152', '-73.0455', 'Suffolk');

如何使用UltraEdit或任何其他工具完成此操作？

Answer 1

这在Notepad++中是可行的：

搜索：VALUES\s+\(\s*('.+?'),\s*('.+?')(.+)\)

替换：VALUES \(SELECT C.id FROM CITY C WHERE C.name = $1 and C.state = $2 $3\)