oracle一条记录分隔成多条记录

oracle查询的记录包含类似a,b,c的记录,在此希望做到根据指定的字符如逗号分隔成多条记录。以下是简单的示例:

select regexp_substr(‘a,b,c,d’, ‘[^,]+’, 1, rownum),
length(‘a,b,c,d’) – length(replace(‘a,b,c,d’, ‘,’, ”)) + 1,
rownum,
length(replace(‘a,b,c,d’, ‘,’, ”)),
length(‘a,b,c,d’)
from dual
connect by rownum <=
length(‘a,b,c,d’) – length(replace(‘a,b,c,d’, ‘,’, ”)) + 1;

原值:
select diccode from DDZH_DDRZ_DIC where dicclass=’安监过滤标记’;

DICCODE
————————————————–
XHGZBJ-1,XHGZBJ-2

1 rows selected

分隔后:

select * from (
select regexp_substr((select diccode from DDZH_DDRZ_DIC where dicclass=’安监过滤标记’), ‘[^,]+’, 1, rownum) diccode
from dual a
connect by rownum <=4) where diccode is not null

DICCODE
—————————————————————————————————-
XHGZBJ-1
XHGZBJ-2

2 rows selected

//参考:

http://www.cnblogs.com/cczz_11/p/3413679.html

http://jingyan.baidu.com/article/cdddd41c56610953cb00e1f5.html

This entry was posted in Database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s