oracle 10g 使用REGEXP_SUBSTR 分拆字符串

SELECT l_count, REGEXP_SUBSTR('add, daddf, dsdf, asdfa, dsfasd, dsfad','[^,]+',1,l_count) AS NAME
  FROM dual
      ,(SELECT LEVEL l_count FROM DUAL CONNECT BY LEVEL<=100)
WHERE l_count <=LENGTH('add, daddf, dsdf, asdfa, dsfasd, dsfad') - LENGTH(REPLACE('add, daddf, dsdf, asdfa, dsfasd, dsfad',','))+1

查询结果为：
lcount   name
1            add
2            daddf
3            dsdf
4            asdfa
5            dsfasd
6             dsfad

 

Oracle 9i 没有 REGEXP_SUBSTR 函数， 可以这么写：

v_changepricelist :=',add, daddf, dsdf, asdfa, dsfasd, dsfad,'

SELECT l_count,

           substr(v_changepricelist,

                  instr(v_changepricelist, ',', 1, l_count) + 1,

                  instr(v_changepricelist, ',', 1, l_count + 1) -

                  instr(v_changepricelist, ',', 1, l_count) - 1) AS meidOrPrice

      FROM dual,

           (SELECT LEVEL l_count

              FROM DUAL

            CONNECT BY LEVEL <= LENGTH(v_changepricelist) -

                       LENGTH(REPLACE(v_changepricelist, ',')) - 1);

查询结果为：

lcount   number
1           1009999
2           11
3            222222
4            333333

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

转载于:http://blog.itpub.net/15742414/viewspace-605933/