近日,朋友发送了一个SQL文件给我,他的日期在数据库中是Varchar类型,而我是date类型,
该文件有2万个Insert语句,类似
insert into T_ZX_HOLIDAY (CDATE, CWEEK, IS_HOLIDAY)
values ('2006-01-01', 1, '1');
insert into T_ZX_HOLIDAY (CDATE, CWEEK, IS_HOLIDAY)
values ('2006-01-02', 2, '0');
insert into T_ZX_HOLIDAY (CDATE, CWEEK, IS_HOLIDAY)
values ('2006-01-03', 3, '0');
.......
我不得不将该插入语句改成
insert into T_ZX_HOLIDAY (CDATE, CWEEK, IS_HOLIDAY)
values (to_date('2006-01-01','yyyy-mm-dd'), 1, '1');
insert into T_ZX_HOLIDAY (CDATE, CWEEK, IS_HOLIDAY)
values (to_date('2006-01-02','yyyy-mm-dd'), 2, '0');
insert into T_ZX_HOLIDAY (CDATE, CWEEK, IS_HOLIDAY)
values (to_date('2006-01-03','yyyy-mm-dd'), 3, '0');
.....
非鱼说用宏和UltraEdit可以做到,但是语句不是同一行的:(
GTM说写个Java类
replaceAll.("value ('.*?',","value (todate'*?'"))
也可以完成,但我觉得这样做仍然麻烦。
我查了一下Emeditor语法及参考了
http://bbs.et8.net/bbs/showthread.php?t=652159
用Emditor的正则替换功能(Perl语法)
查找:'\d\d\d\d-\d\d-\d\d'
替换为:todate(\0,'yyyy-mm-dd')
work done!
\0表示匹配上一次的匹配结果
如果你直接用Perl命令,(这些东西需要转义,多了就麻烦,我还是比较喜欢Emditor/Editplus.
该文件有2万个Insert语句,类似
insert into T_ZX_HOLIDAY (CDATE, CWEEK, IS_HOLIDAY)
values ('2006-01-01', 1, '1');
insert into T_ZX_HOLIDAY (CDATE, CWEEK, IS_HOLIDAY)
values ('2006-01-02', 2, '0');
insert into T_ZX_HOLIDAY (CDATE, CWEEK, IS_HOLIDAY)
values ('2006-01-03', 3, '0');
.......
我不得不将该插入语句改成
insert into T_ZX_HOLIDAY (CDATE, CWEEK, IS_HOLIDAY)
values (to_date('2006-01-01','yyyy-mm-dd'), 1, '1');
insert into T_ZX_HOLIDAY (CDATE, CWEEK, IS_HOLIDAY)
values (to_date('2006-01-02','yyyy-mm-dd'), 2, '0');
insert into T_ZX_HOLIDAY (CDATE, CWEEK, IS_HOLIDAY)
values (to_date('2006-01-03','yyyy-mm-dd'), 3, '0');
.....
非鱼说用宏和UltraEdit可以做到,但是语句不是同一行的:(
GTM说写个Java类
replaceAll.("value ('.*?',","value (todate'*?'"))
也可以完成,但我觉得这样做仍然麻烦。
我查了一下Emeditor语法及参考了
http://bbs.et8.net/bbs/showthread.php?t=652159
用Emditor的正则替换功能(Perl语法)
查找:'\d\d\d\d-\d\d-\d\d'
替换为:todate(\0,'yyyy-mm-dd')
work done!
\0表示匹配上一次的匹配结果
如果你直接用Perl命令,(这些东西需要转义,多了就麻烦,我还是比较喜欢Emditor/Editplus.