无分隔符的字段分组拆分

前几天解答的题目

把下面例子中fq字段按每个字符来分组拆分

原始数据

SEASON	MONTH	CODE	NO	FQ	DAYS
17S_A	4	AB	8001	1234567	7
18S_A	4	CD	8002	246	3
19S_A	5	EF	8003	15	2

要实现的结果

SEASON    MONTH    CODE    NO    FQ
17S_A    4    AB    8001    1
17S_A    4    AB    8001    2
17S_A    4    AB    8001    3
17S_A    4    AB    8001    4
17S_A    4    AB    8001    5
17S_A    4    AB    8001    6
17S_A    4    AB    8001    7
18S_A    4    CD    8002    2
18S_A    4    CD    8002    4
18S_A    4    CD    8002    6
19S_A    5    EF    8003    1
19S_A    5    EF    8003    5

SQL:

1. with d as
2.  (select '17S_A' as season,
3.          4 as month,
4.          'AB' as code,
5.          8001 as no,
6.          1234567 as fq,
7.          7 as days
8.     from dual
9.   union all
10.   select '18S_A' as season,
11.          4 as month,
12.          'CD' as code,
13.          8002 as no,
14.          246 as fq,
15.          3 as days
16.     from dual
17.   union all
18.   select '19S_A', 5, 'EF', 8003, 15, 2 from dual)
19. select season, month, code, no, substr(fq, level, 1) fq
20.   from d
21. connect by level <= length(fq)
22.        and season = prior season
23.        and month = prior month
24.        and code = prior code
25.        and no = prior no
26.        and prior dbms_random.value is not null;

下面是按照两个字节拆分的例子

1. with d as
2.  (select '17S_A' as season,
3.          4 as month,
4.          'AB' as code,
5.          8001 as no,
6.          1234567 as fq,
7.          7 as days
8.     from dual
9.   union all
10.   select '18S_A' as season,
11.          4 as month,
12.          'CD' as code,
13.          8002 as no,
14.          246 as fq,
15.          3 as days
16.     from dual
17.   union all
18.   select '19S_A', 5, 'EF', 8003, 15, 2 from dual)
19. select season, month, code, no, substr(fq, 2 * (level - 1) + 1, 2) fq
20.   from d
21. connect by level < length(fq) / 2 + 1
22.        and season = prior season
23.        and month = prior month
24.        and code = prior code
25.        and no = prior no
26.        and prior dbms_random.value is not null;

结果：

SEASON	MONTH	CODE	NO	FQ
17S_A	4	AB	8001	12
17S_A	4	AB	8001	34
17S_A	4	AB	8001	56
17S_A	4	AB	8001	7
18S_A	4	CD	8002	24
18S_A	4	CD	8002	6
19S_A	5	EF	8003	15