假设我的表 ZD中,有个字段DJH,
其内容如下所示:8-7-1-1
8-7-10-2
想吧他们替换为统一12位的,第一个数字占两位,第二,第三个数字各占三位,最后一个数字占 4位,去掉所有"-"
080070010001
080070100002应该怎么写Sql语句,
其内容如下所示:8-7-1-1
8-7-10-2
想吧他们替换为统一12位的,第一个数字占两位,第二,第三个数字各占三位,最后一个数字占 4位,去掉所有"-"
080070010001
080070100002应该怎么写Sql语句,
SQL>
SQL> SELECT substr(to_char(substr('8-7-1-1',1,instr('8-7-1-1','-',1,1)-1),'00'),2)||
2 substr(to_char(substr('8-7-1-1',instr('8-7-1-1','-',1,1)+1,instr('8-7-1-1','-',1,2)-instr('8-7-1-1','-',1,1)-1),'000'),2)||
3 substr(to_char(substr('8-7-1-1',instr('8-7-1-1','-',1,2)+1,instr('8-7-1-1','-',1,3)-instr('8-7-1-1','-',1,2)-1),'000'),2)||
4 substr(to_char(substr('8-7-1-1',instr('8-7-1-1','-',1,3)+1),'0000'),2)
5 FROM dual
6 ;
SUBSTR(TO_CHAR(SUBSTR('8-7-1-1
------------------------------
080070010001
SQL>
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as b
SQL>
SQL> SELECT REPLACE(WMSYS.WM_CONCAT(LPAD(C3, 3, '0')), ',', '')
2 FROM (SELECT '1' ID,
3 SUBSTR(C3,
4 DECODE(ROWNUM, 1, 1, INSTR(C3, '-', 1, ROWNUM - 1) + 1),
5 INSTR(C3, '-', 1, ROWNUM) -
6 DECODE(ROWNUM, 1, 1, INSTR(C3, '-', 1, ROWNUM - 1) + 1)) C3
7 FROM (SELECT '8-10-1-' C3 FROM DUAL)
8 CONNECT BY ROWNUM <= LENGTH(C3) - LENGTH(REPLACE(C3, '-', '')))
9 GROUP BY ID
10 ;
REPLACE(WMSYS.WM_CONCAT(LPAD(C
--------------------------------------------------------------------------------
008010001
SQL>
SELECT lpad(substr('8-7-10-2',1,instr('8-7-10-2','-',1,1)-1),2,'0') ||
lpad(substr('8-7-10-2',instr('8-7-10-2','-',1,1)+1,instr('8-7-10-2','-',1,2)-instr('8-7-10-2','-',1,1)-1),3,'0') ||
lpad(substr('8-7-10-2',instr('8-7-10-2','-',1,2)+1,instr('8-7-10-2','-',1,3)-instr('8-7-10-2','-',1,2)-1),3,'0') ||
lpad(substr('8-7-10-2',instr('8-7-10-2','-',1,3)+1),4,'0') FROM dual;
SELECT '8-7-1-1' a FROM dual
UNION ALL SELECT '8-7-10-2' FROM dual)SELECT LPAD(regexp_substr(a,'[[:digit:]]+',1,1),2,'0')||
LPAD(regexp_substr(a,'[[:digit:]]+',1,2),3,'0')||
LPAD(regexp_substr(a,'[[:digit:]]+',1,3),3,'0')||
LPAD(regexp_substr(a,'[[:digit:]]+',1,4),4,'0') new_col
FROM table1;