时间戳

首页 > 解决方案 > 使用分隔值拆分记录

oracle - 使用分隔值拆分记录

问题描述

我需要将分隔的字符串值分成单独的行我需要的是：

FAILUREID  TNO
1          t01
1          t02
1          t03
2          t04
2          t05

但是使用下面的代码我收到了

1   t01
1   t02
1   t03
1   t03
1   t03
1   t02
1   t03
2   t04
2   t05
2   t05

有些行是重复的。以下代码中的错误在哪里，如何修复？（不同的不是解决方案）

with data as 
( select 1 as FailureID
        ,'t01;t02;t03' as Tickets
  from dual
  union 
  select 2 
        ,'t04;t05'
    from dual
)
select   FailureID
        ,regexp_substr(Tickets, '[^;]+', 1, level) as TNO
from data
connect by regexp_substr(tickets, '[^;]+', 1, level) is not null
order by failureid

标签： oracle

解决方案

方法如下（区别从第 13 行开始）：

SQL> with data as
  2  ( select 1 as FailureID
  3          ,'t01;t02;t03' as Tickets
  4    from dual
  5    union
  6    select 2
  7          ,'t04;t05'
  8      from dual
  9  )
 10  select   FailureID
 11          ,regexp_substr(Tickets, '[^;]+', 1, column_value) as TNO
 12  from data
 13  cross join table(cast(multiset(select level from dual
 14                                 connect by level <= regexp_count(tickets, ';') + 1
 15                                ) as sys.odcinumberlist))
 16  order by failureid;

 FAILUREID TNO
---------- -----------
         1 t01
         1 t02
         1 t03
         2 t04
         2 t05

SQL>

推荐阅读