你这个分割的规则是什么?看不懂 将前四个分割成a1,第五个到第七个分割成a2,第八个到第九个分割成a3,剩余的是a4 with t as (select 'SDXY037022601' str from dual union all select 'BSHT0120112' str from dual) select ltrim(substr(str, decode(level, 1, 1, 2, 5, 3, 8, 10), decode(level, 1, 4, 2, 3, 3, 2, length(str) - 9)), '0') from (select str, rownum rn from t) connect by level <= 4 and prior rn = rn and prior dbms_random.value is not null;
SQL> with t as 2 (select 'SDXY037022601' str 3 from dual 4 union all 5 select 'BSHT0120112' str 6 from dual) 7 select substr(str,1,4) a1,substr(str,5,3) a2,substr(str,8,2) a3,substr(str,10) a4 8 from t;A1 A2 A3 A4 -------- ------ ---- -------- SDXY 037 02 2601 BSHT 012 01 12
你这个分割的规则是什么?看不懂 将前四个分割成a1,第五个到第七个分割成a2,第八个到第九个分割成a3,剩余的是a4 with t as (select 'SDXY037022601' str from dual union all select 'BSHT0120112' str from dual) select ltrim(substr(str, decode(level, 1, 1, 2, 5, 3, 8, 10), decode(level, 1, 4, 2, 3, 3, 2, length(str) - 9)), '0') from (select str, rownum rn from t) connect by level <= 4 and prior rn = rn and prior dbms_random.value is not null;
with t as (select 'SDXY037022601' str from dual union all select 'BSHT0120112' str from dual) SELECT T.STR,SUBSTR(T.STR,1,4) A,TO_NUMBER(SUBSTR(T.STR,5,3)) B,TO_NUMBER(SUBSTR(T.STR,8,2)) C,TO_NUMBER(SUBSTR(T.STR,9)) D FROM T截取完直接转换成数字就可以了
将前四个分割成a1,第五个到第七个分割成a2,第八个到第九个分割成a3,剩余的是a4
将前四个分割成a1,第五个到第七个分割成a2,第八个到第九个分割成a3,剩余的是a4 with t as
(select 'SDXY037022601' str
from dual
union all
select 'BSHT0120112' str
from dual)
select ltrim(substr(str,
decode(level, 1, 1, 2, 5, 3, 8, 10),
decode(level, 1, 4, 2, 3, 3, 2, length(str) - 9)),
'0')
from (select str, rownum rn from t)
connect by level <= 4
and prior rn = rn
and prior dbms_random.value is not null;
2 (select 'SDXY037022601' str
3 from dual
4 union all
5 select 'BSHT0120112' str
6 from dual)
7 select substr(str,1,4) a1,substr(str,5,3) a2,substr(str,8,2) a3,substr(str,10) a4
8 from t;A1 A2 A3 A4
-------- ------ ---- --------
SDXY 037 02 2601
BSHT 012 01 12
将前四个分割成a1,第五个到第七个分割成a2,第八个到第九个分割成a3,剩余的是a4 with t as
(select 'SDXY037022601' str
from dual
union all
select 'BSHT0120112' str
from dual)
select ltrim(substr(str,
decode(level, 1, 1, 2, 5, 3, 8, 10),
decode(level, 1, 4, 2, 3, 3, 2, length(str) - 9)),
'0')
from (select str, rownum rn from t)
connect by level <= 4
and prior rn = rn
and prior dbms_random.value is not null;
没看懂,有没有简单易懂的啊
select substr(str,1,4) a1,substr(str,5,3) a2,substr(str,8,2) a3,substr(str,10) a4这么写怎么去0啊
select substr(str,1,4) a1,substr(str,5,3) a2,substr(str,8,2) a3,substr(str,10) a4这么写怎么去0啊
select ltrim(substr(str,1,4),'0') a1,ltrim(substr(str,5,3),'0') a2,ltrim(substr(str,8,2),'0') a3,ltrim(substr(str,10),'0') a4 from t;
(select 'SDXY037022601' str
from dual
union all
select 'BSHT0120112' str
from dual)
SELECT T.STR,SUBSTR(T.STR,1,4) A,TO_NUMBER(SUBSTR(T.STR,5,3)) B,TO_NUMBER(SUBSTR(T.STR,8,2)) C,TO_NUMBER(SUBSTR(T.STR,9)) D FROM T截取完直接转换成数字就可以了
select substr(str,1,4) a1,substr(str,5,3) a2,substr(str,8,2) a3,substr(str,10) a4这么写怎么去0啊
select ltrim(substr(str,1,4),'0') a1,ltrim(substr(str,5,3),'0') a2,ltrim(substr(str,8,2),'0') a3,ltrim(substr(str,10),'0') a4 from t;
那还想把被分割的字符串作为表中的a5该怎么处理啊