表:XXX
字段A:
内容:TLM_KERL_TZ_1-17-1-0102
TLM_KERL_TZ_2-17-2-0102 怎么变成TLM_KERL_TZ_01-17-01-0102 ,
TLM_KERL_TZ_02-17-02-0102 ,
用SQL语句写
字段A:
内容:TLM_KERL_TZ_1-17-1-0102
TLM_KERL_TZ_2-17-2-0102 怎么变成TLM_KERL_TZ_01-17-01-0102 ,
TLM_KERL_TZ_02-17-02-0102 ,
用SQL语句写
-----------------
01SQL> select to_char('11','00') from dual;TO_CHAR('11','00')
------------------
11SQL>
SQL> select replace('TLM_KERL_TZ_1-17-1-0102 ','1-','01-') from dual;
REPLACE('TLM_KERL_TZ_1-17-1-01
------------------------------
TLM_KERL_TZ_01-17-01-0102
update XXX t set t.A = substr(t.A, 1, 12) || '0' || substr(t.A, 13, 11)
select replace('TLM_KERL_TZ_1-17-1-0102 ','-1-','-01-') from dual;
update 表名 set a=replace(a,substr(a,instr(a,'-',-1,2)-2,3),substr(a,instr(a,'-',-1,2)-2,3)||'0');
a=regexp_replace(
a,
regexp_substr(a,'[[:digit:]]{1,2}-..-'),
rpad(lpad(regexp_subst(a,'[[:digit:]]{1,2}-..-'),6,'0'),7,'0')
);这个要在oracle 10g中才能运行。