表字段中含有分隔符提取各项内容

直接上图

SQL> select * from test_str;

ID STR
---------- --------------------------------------------------------------------------------
1 0201,0202,0203,0204
2 0101,0102,0103,0104

SQL>
SQL> select distinct t.id, regexp_substr(t.str, '[^,]+', 1, level)
2 from test_str t
3 connect by level <= length(t.str) - length(replace(t.str, ',', '')) + 1
4 order by regexp_substr(t.str, '[^,]+', 1, level);

ID REGEXP_SUBSTR(T.STR,'[^,]+',1,
---------- --------------------------------------------------------------------------------
2 0101
2 0102
2 0103
2 0104
1 0201
1 0202
1 0203
1 0204

8 rows selected

SQL>

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16298743/viewspace-1057139/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16298743/viewspace-1057139/