我要把SELECT * FROM DM_CKTS_QYLX@DBL_BM WHERE 1=1 替换成 SELECT * FROM BM.DM_CKTS_QYLX WHERE 1=1 该怎么替换?注意SELECT * FROM DM_CKTS_QYLX@DBL_BM WHERE 1=1 是一个字段source_sql的值
update sql set sql=regexp_like(sql,'[A-Z]{2}_[A-Z]\1','BM.DM_CKTS_QYLX') where regexp_like(sql,'[A-Z]{2}_[A-Z]\1');
忘记了一个括号 update sql set sql=regexp_like(sql, '[A-Z]{2}(_[A-Z])\1 ', 'BM.DM_CKTS_QYLX ') where regexp_like(sql, '[A-Z]{2}_[A-Z]\1 ');
看来还是我没有说明白,SELECT * FROM DM_CKTS_QYLX@DBL_BM WHERE 1=1只是该字段的一条数据,还有其他的,并不一定所有的表名都叫DM_CKTS_QYLX的,还有例如DM_DJZCLX@DBL_BM 这样的。
就是把 SELECT * FROM DM_CKTS_QYLX@DBL_BM WHERE 1=1 SELECT * FROM DM_DJZCLX@DBL_BM WHERE 1=1 SELECT * FROM DM_SWJG@DBL_BM WHERE 1=1 SELECT * FROM DM_SZ@DBL_BM WHERE 1=1 update成 SELECT * FROM BM.DM_CKTS_QYLX WHERE 1=1 SELECT * FROM BM.DM_DJZCLX WHERE 1=1 SELECT * FROM BM.DM_SWJG WHERE 1=1 SELECT * FROM BM.DM_SZ WHERE 1=1
SELECT TRIM(SUBSTR(source_sql,1,INSTR(source_sql,'FROM')+4))||' BM.'||TRIM(REPLACE(SUBSTR(source_sql,INSTR(source_sql,'FROM')+4,LENGTH(source_sql)),'@DBL_BM','')) FROM YOURTABLE
INSTR現在有记不清是子串才前面还是子串在后面了
可以分析字符串,找出' where '和' from '的位置,然后截取中间的部分去掉空格+"补充的字符串"
update sql set sql=regexp_like(sql, '[A-Z]{2}(_[A-Z])\1 ', 'BM.DM_CKTS_QYLX ') where regexp_like(sql, '[A-Z]{2}_[A-Z]\1 ');
SELECT * FROM DM_CKTS_QYLX@DBL_BM WHERE 1=1
SELECT * FROM DM_DJZCLX@DBL_BM WHERE 1=1
SELECT * FROM DM_SWJG@DBL_BM WHERE 1=1
SELECT * FROM DM_SZ@DBL_BM WHERE 1=1
update成
SELECT * FROM BM.DM_CKTS_QYLX WHERE 1=1
SELECT * FROM BM.DM_DJZCLX WHERE 1=1
SELECT * FROM BM.DM_SWJG WHERE 1=1
SELECT * FROM BM.DM_SZ WHERE 1=1
FROM YOURTABLE