oracle用分隔符分割字段,并转为多行

作用:当判断是否满足条件,而条件字段又是用","分割的时候。

 如果数据库中一个字段插入数据,并且以","分割。有点像行转列

　　4804510101,4946645450,4870799370,4386453634,19563423　

     select ur2.c_orgunitid
　　p2.c_ranges
　　from TB_WK_PRIZE p2
　　inner join (select level l from dual connect by level <= 100) b
　　on b.l <= length(p2.c_ranges) - length(replace(p2.c_ranges, ',')) + 1
　　inner join TB_ORG_UNITRELATION ur1 on ur1.c_orgunitid=regexp_substr(p2.c_ranges, '[^,]+', 1, l) and ur1.c_status=1
　　inner join TB_ORG_UNITRELATION ur2 on ur2.c_reportcode like ur1.c_reportcode||'%' and ur2.c_status=1;
　　

　　select nvl(REGEXP_SUBSTR('89,23,14', '[^,]+',1 ,LEVEL , 'i'), 'null') as str from dual;
　　connect by LEVEL <= 3

---

 

  注释： REGEXP_SUBSTR