WITH tab AS( SELECT 123456 num FROM dual )SELECT num, To_Number(substr(num,1,3-1)||'0'||SubStr(num,3)) num1 , --第3位插入一个0 To_Number(substr(num,1,3-1)||SubStr(num,3+1)) num2 --删除第3位 FROM tabNUM NUM1 NUM2 ------------------------------- 123456 1203456 12456
SQL> select * from temp; ID ---------- 123456SQL> ---修改此数字中的第几位 SQL> update temp set id = to_number(replace(to_char(id),substr(id,4,1),'9'));1 row updatedSQL> commit;Commit completeSQL> select * from temp; ID ---------- 123956SQL> ----删除此数字中的第几位 SQL> update temp set id = to_number(replace(to_char(id),substr(id,4,1),''));1 row updatedSQL> select * from temp; ID ---------- 12356SQL> ---添加数字插入到第几位 SQL> update temp set id = to_number(substr(id,0,4-1)||'1'||substr(id,4,length(id)-1)); 2 、 3 /update temp set id = to_number(substr(id,0,4-1)||'1'||substr(id,4,length(id)-1)); 、ORA-00911: 无效字符SQL> update temp set id = to_number(substr(id,0,4-1)||'1'||substr(id,4,length(id)-1));1 row updatedSQL> commit;Commit completeSQL> select * from temp; ID ---------- 123156SQL>
只要知道規則,樓主可以自己寫一些function來實現
--replace substr等函数 --修改此数字中的 第几位 update tb set col=replace(col,substr(col,n,1),'修改目标数字')--第几位插入一个数字 update tb set col=replace(col,substr(col,n,1),substr(col,n,1)||'插入目标数字')--删除一位数 update tb set col=replace(col,substr(col,n,1),'')
SQL> edi 已写入 file afiedt.buf 1 select replace('a23',substr('a23',2,1),'3'), 2 replace('a23',substr('a23',2,1),'35'), 3 replace('a23',substr('a23',2,1),'') 4* from dual SQL> /REP REPL RE --- ---- -- a33 a353 a3
SELECT 123456 num FROM dual
)SELECT num,
To_Number(substr(num,1,3-1)||'0'||SubStr(num,3)) num1 , --第3位插入一个0
To_Number(substr(num,1,3-1)||SubStr(num,3+1)) num2 --删除第3位
FROM tabNUM NUM1 NUM2
-------------------------------
123456 1203456 12456
SQL> select * from temp; ID
----------
123456SQL> ---修改此数字中的第几位
SQL> update temp set id = to_number(replace(to_char(id),substr(id,4,1),'9'));1 row updatedSQL> commit;Commit completeSQL> select * from temp; ID
----------
123956SQL> ----删除此数字中的第几位
SQL> update temp set id = to_number(replace(to_char(id),substr(id,4,1),''));1 row updatedSQL> select * from temp; ID
----------
12356SQL> ---添加数字插入到第几位
SQL> update temp set id = to_number(substr(id,0,4-1)||'1'||substr(id,4,length(id)-1));
2 、
3 /update temp set id = to_number(substr(id,0,4-1)||'1'||substr(id,4,length(id)-1));
、ORA-00911: 无效字符SQL> update temp set id = to_number(substr(id,0,4-1)||'1'||substr(id,4,length(id)-1));1 row updatedSQL> commit;Commit completeSQL> select * from temp; ID
----------
123156SQL>
--修改此数字中的 第几位
update tb set col=replace(col,substr(col,n,1),'修改目标数字')--第几位插入一个数字
update tb set col=replace(col,substr(col,n,1),substr(col,n,1)||'插入目标数字')--删除一位数
update tb set col=replace(col,substr(col,n,1),'')
已写入 file afiedt.buf 1 select replace('a23',substr('a23',2,1),'3'),
2 replace('a23',substr('a23',2,1),'35'),
3 replace('a23',substr('a23',2,1),'')
4* from dual
SQL> /REP REPL RE
--- ---- --
a33 a353 a3
已写入 file afiedt.buf 1 select
2 replace(23,substr(23,2,1),3),
3 replace(23,substr(23,2,1),35),
4 replace(23,substr(23,2,1),'')
5* from dual
SQL> /RE REP R
-- --- -
23 235 2
--一样的