- 需求
Oracle导出的sql 语句,需要调整日期格式,修改字段,数据量比较大,notepad操作列操作不方便。
样式
Insert into BOXTAG (DATETIME,BILL,RSSI,SPL) values ('2020-04-09 16:53:32','20200409165332',0,0);
Insert into BOXTAG (DATETIME,BILL,RSSI,SPL) values ('2020-04-09 16:53:32','20200409165332',0,0);
Insert into BOXTAG (DATETIME,BILL,RSSI,SPL) values ('2020-04-09 16:53:33','20200409165333',0,0);
Insert into BOXTAG (DATETIME,BILL,RSSI,SPL) values ('2020-04-09 16:53:34','20200409165334',0,0);
Insert into BOXTAG (DATETIME,BILL,RSSI,SPL) values ('2020-04-09 16:53:34','20200409165334',0,0);
Insert into BOXTAG (DATETIME,BILL,RSSI,SPL) values ('2020-04-09 16:53:35','20200409165335',0,0);
Insert into BOXTAG (DATETIME,BILL,RSSI,SPL) values ('2020-04-09 16:53:36','20200409165336',0,0);
将日期字段修改为Oracle date类型,如下格式
Insert into BOXTAGNEW (DATETIME,FLAG,BILL,RSSI,SPL) values (to_date('08-1月 -21','DD-MON-RR'),0,'20210108140258',0,0);
Insert into BOXTAGNEW (DATETIME,FLAG,BILL,RSSI,SPL) values (to_date('08-1月 -21','DD-MON-RR'),0,'20210108140258',0,0);
Insert into BOXTAGNEW (DATETIME,FLAG,BILL,RSSI,SPL) values (to_date('08-1月 -21','DD-MON-RR'),0,'20210108140259',0,0);
Insert into BOXTAGNEW (DATETIME,FLAG,BILL,RSSI,SPL) values (to_date('08-1月 -21','DD-MON-RR'),0,'20210108140300',0,0);
Insert into BOXTAGNEW (DATETIME,FLAG,BILL,RSSI,SPL) values (to_date('08-1月 -21','DD-MON-RR'),0,'20210108140301',0,0);
Insert into BOXTAGNEW (DATETIME,FLAG,BILL,RSSI,SPL) values (to_date('08-1月 -21','DD-MON-RR'),0,'20210108140301',0,0);
- 简单使用c# 读写文件来修改,其中日期格式使用正则表达匹配替换sql脚本。
替换yyyy-MM-dd hh:mm:ss 或者yyyy/MM/dd hh:mm:ss 两种格式的时间字符串
主要代码
Regex regex = new Regex(@"'\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}'");
Match match = regex.Match(line);//line--sql语句
if (string.IsNullOrEmpty(match.Value))
{
regex = new Regex(@"'\d{4}/\d{2}/\d{2} \d{2}:\d{2}:\d{2}'");
match = regex.Match(line);
}
//match.Value 为匹配到的值
string date = $"to_date(substr({match.Value}, 1, 10), 'yyyy-mm-dd')";
//替换文本中的值
string sql= regex.Replace(line, date);