Oracle Database 使用递归子查询分解子句拆分字符串
示例
样本数据：
CREATE TABLE table_name ( id, list ) AS SELECT 1, 'a,b,c,d' FROM DUAL UNION ALL -- Multiple items in the list SELECT 2, 'e' FROM DUAL UNION ALL -- Single item in the list SELECT 3, NULL FROM DUAL UNION ALL -- NULL list SELECT 4, 'f,,g' FROM DUAL; -- NULL item in the list
查询：
WITH bounds ( id, list, start_pos, end_pos, lvl ) AS (
SELECT id, list, 1, INSTR( list, ',' ), 1 FROM table_name
UNION ALL
SELECT id,
list,
end_pos + 1,
INSTR( list, ',', end_pos + 1 ),
lvl + 1
FROM bounds
WHERE end_pos > 0
)
SELECT id,
SUBSTR(
list,
start_pos,
CASE end_pos
WHEN 0
THEN LENGTH( list ) + 1
ELSE end_pos
END - start_pos
) AS item,
lvl
FROM bounds
ORDER BY id, lvl;输出：
ID ITEM LVL
---------- ------- ----------
1 a 1
1 b 2
1 c 3
1 d 4
2 e 1
3 (NULL) 1
4 f 1
4 (NULL) 2
4 g 3
热门推荐
10 新年门口花束祝福语简短
11 盘锦结婚祝福语大全简短
12 父母生日祝福语 简短独特
13 家庭恩爱祝福语简短英文
14 高考俄语祝福语大全简短
15 虎年祝福语 诗句唯美简短
16 生日婚礼祝福语简短精辟
17 虎年喝酒拜年祝福语简短
18 教师闺蜜祝福语简短