17,086
社区成员
发帖
与我相关
我的任务
分享
with t as
(select 1 AS rd, '0123456789' name
from dual
UNION ALL
SELECT 2 AS RD, 'ABCDWQWERA' name from dual)
select row_number() over(order by a) rd, a
from (select DECODE(LENGTH(NAME) / 5,
0,
SUBSTR(NAME, -LENGTH(NAME), LENGTH(NAME)),
substr(name, 0, 5)) A
FROM T
connect by LEVEL < (SELECT SUM(LENGTH(NAME) / 5 - 1) FROM T)
union all
select REPLACE(NAME,
DECODE(LENGTH(NAME) / 5,
0,
SUBSTR(NAME, -LENGTH(NAME), LENGTH(NAME)),
substr(name, 0, 5)),
'') a
from t
connect by LEVEL < (SELECT SUM(LENGTH(NAME) / 5 - 1) FROM T))
结果:
RD A
1 1 01234
2 2 56789
3 3 ABCDW
4 4 QWERA
我这里限制了NAME字段只能小于等于10个字符串长度,且固定用5个长度去截取.,