[oracle]逗号(或分号等)间隔的列,按这一列劈分后转多行

今天遇到个需求,要匹配两个表, 但是关联的字段,在另一个表中是放在一个字段中用分号分割的

怎么全部匹配呢?

后来在网上搜到了, 记录下


SELECT cp.ES_EXTNUMBER,

cp.ES_AGENTID,

cp.ES_CHSTATESTR,

(sysdate-cp.ES_STARTDATETIME)*24*3600 es_times,

REGEXP_SUBSTR(CP.ES_GROUP, '[^;]+', 1, L) AS ES_GROUP

,L

FROM aladdinpbx_extstate CP,

(SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 10)

WHERE L(+) <= LENGTH(CP.ES_GROUP) - LENGTH(REPLACE(CP.ES_GROUP, ';')) + 1

order by ES_EXTNUMBER


[url]http://dl.iteye.com/upload/picture/pic/136177/487baa3f-f642-3fe4-b573-4785d7c2a061-thumb.png[/url]