关于字符串拆分的问题

Question

数据如下： with testtab as (
select 1 id, '1,2,3' str from dual
union all
select 2, '2, 4' from dual
)
select * from testtab;想要达到的查询效果是
id  str
1 1
1 2
1 3
2 2
2 4现在数据库里面，已经有他人建的一个函数，作用是拆分字符串，但是只是针对一个字符串的
不知道能否利用这个函数达到我的查询目的。函数使用说明如下：
select * from table(str_split('1, 2, 3'));返回
1
2
3

Answer 1

你是想id一次插入一个值，str想一次插入多个值？我觉得行不通

Answer 2

已经建好了拆分字符串的函数，楼主直接用就可以了啊 
也可以自己拆分：
SELECT REGEXP_SUBSTR((select wm_concat(str) from testtab), '[^,]+', 1, N) COL1
  FROM DUAL,
       (SELECT ROWNUM N
          FROM DUAL
        CONNECT BY ROWNUM <
                   LENGTH(REGEXP_REPLACE((select wm_concat(str) from testtab), '[^,]', NULL)) + 2)
 WHERE N <= LENGTH(REGEXP_REPLACE((select wm_concat(str) from testtab), '[^,]', NULL)) + 1;COL1
--------------------------------------------------------------------------------
1
2
3
2
4

Answer 3

with testtab as (
select 1 id, '1,2,3' str from dual
union all
select 2, '2,4' from dual
)
SELECT DISTINCT ID,substr(col
               ,DECODE(LEVEL, 1, 1, instr(col, ',', 1, LEVEL - 1) + 1)
               ,DECODE(instr(col, ',', 1, LEVEL)
                     ,0
                      ,LENGTH(COL) + 1
                    ,instr(col, ',', 1, LEVEL)) -
               DECODE(LEVEL, 1, 1, instr(col, ',', 1, LEVEL - 1) + 1)) col
FROM (SELECT ID,str col FROM testtab)
CONNECT BY LEVEL <= length(translate(col, ',' || col, ',')) + 1;

Answer 4

数据量小，就用下面这个最简单的写法，利用正则：
with testtab as
 (select 1 id, '1,2,3' str
    from dual
  union all
  select 2, '2,4' from dual)
select DISTINCT ID, REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL) STR
  from testtab
CONNECT BY LEVEL <= REGEXP_COUNT(STR, ',') + 1
ORDER BY ID数据量大就用这个写法试试，去掉distinct：
with testtab as
 (select 1 id, '1,2,3' str
    from dual
  union all
  select 2, '2,4' from dual)
select ID, REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL) STR
  from testtab
CONNECT BY LEVEL <= REGEXP_COUNT(STR, ',') + 1
       and id = prior id
       and prior dbms_random.value is not null
 ORDER BY ID

Answer 5

REGEXP_COUNT应该是在11G上
我用10G写了一个demo

Answer 6

ls的用到distinct  我的数据量还是比较大的（上k）  所以还是u010412956的最优

Answer 7

不过我没看懂是怎么达到自循环但又不会重复的，我试过将dbms_random.value 那一行去掉结果数据库报错说数据存在循环加上nocycle，则结果不对