with t as (select '金海湾B区11-1702' str from dual union all select '金海湾B区11-1-1702' str from dual) select regexp_substr(str, '[^0-9]+') a, regexp_substr(str, '[0-9]+') b, decode(regexp_instr(str, '-', 1, 2), 0, '0', regexp_substr(str, '[0-9]+', 1, 2)) c,
regexp_substr(str, '[0-9]+$') d from t;
写成存储过程的话,将获得的这些数据update到原表中,该怎么写啊
with t as (select '金海湾B区11-1702' str from dual union all select '金海湾B区11-1-1702' str from dual) select regexp_substr(str, '[^0-9]+') a, regexp_substr(str, '[0-9]+') b, decode(regexp_instr(str, '-', 1, 2), 0, '0', regexp_substr(str, '[0-9]+', 1, 2)) c,
regexp_substr(str, '[0-9]+$') d from t; 写成存储过程,将获得的这些数据update到原表中,该怎么写啊
那将‘金海湾B区11-1702‘ ’金海湾B区11-1-1702‘进行字符串分割成’金海湾B区‘ ’11‘ ’0‘ ’1702‘和’金海湾B区‘ ’11‘ ’1‘ ’1702‘呢
写个存储过程吧,将分割成的数据update到表中
如果是低版本可以用下面的语句替换
length(temp)-length(replace(temp,'-',''))+1
NVL(LTRIM(STR,'0'),'0')
with t as
(select '金海湾B区11-1702' str
from dual
union all
select '金海湾B区11-1-1702' str
from dual)
select regexp_substr(str, '[^0-9]+') a,
regexp_substr(str, '[0-9]+') b,
decode(regexp_instr(str, '-', 1, 2),
0,
'0',
regexp_substr(str, '[0-9]+', 1, 2)) c,
regexp_substr(str, '[0-9]+$') d
from t;
with t as
(select '金海湾B区11-1702' str
from dual
union all
select '金海湾B区11-1-1702' str
from dual)
select regexp_substr(str, '[^0-9]+') a,
regexp_substr(str, '[0-9]+') b,
decode(regexp_instr(str, '-', 1, 2),
0,
'0',
regexp_substr(str, '[0-9]+', 1, 2)) c,
regexp_substr(str, '[0-9]+$') d
from t;
写成存储过程,将获得的这些数据update到原表中,该怎么写啊
update T
SET A=regexp_substr(str, '[^0-9]+'),
B=regexp_substr(str, '[0-9]+'),
C=decode(regexp_instr(str, '-', 1, 2),
0,
'0',
regexp_substr(str, '[0-9]+', 1, 2)),
D= regexp_substr(str, '[0-9]+$');