对字段值按字符拆分多行方法

1.如果有多行数据需要拆分的话

    

SELECT ename, substr(ename, LEVEL, 1) AS c
FROM   emp
CONNECT BY LEVEL <= length(ename)
           AND PRIOR ROWID = ROWID
           AND PRIOR dbms_random.value() IS NOT NULL;

2.如果只有单行进行拆分

SELECT substr('SMITH', LEVEL, 1)
FROM   dual
CONNECT BY LEVEL <= length('SMITH');

3.补充

SELECT regexp_substr(v.value, '[^,]+', 1, LEVEL)
FROM   (SELECT 1 lv, '1,2,3,4' VALUE
        FROM   dual
        UNION ALL
        SELECT 2, 'a,b,c,d,e'
        FROM   dual) v
CONNECT BY nocycle
 LEVEL <= length(translate(v.value, ',' || v.value, ',')) + 1
           AND PRIOR lv = lv
           AND PRIOR dbms_random.value() IS NOT NULL;

方法有很多，简单举这些例子。