将varchar2解析为表(Oracle) [英] Parse varchar2 to table (Oracle)

问题描述

Oracle DB 11g r2 中是否有内置函数可以将varchar2变量解析为表?与 listagg 或 wm_concat 相反.我发现只有 Tom Kyte 的方法可追溯到2006年:

Is there built-in function in Oracle DB 11g r2 that could parse varchar2 variable to table? Opposite of listagg or wm_concat. I found only Tom Kyte's method dated 2006:

with data as
(
select trim(substr (txt, instr(txt, ',', 1, level) + 1
       , instr(txt, ',', 1, level + 1) - instr(txt, ',', 1, level) - 1)) as token
from (select ',' || :txt || ',' txt from dual)
connect by level <= length(:txt) - length(replace(:txt, ',', '')) + 1
)
select * from data;

我认为Oracle必须有更简单的方法.

I think Oracle must have simpler way.

推荐答案

否.

我会略微简化汤姆的方法，但不会太多.您现在也可以使用正则表达式:

I would simplify Tom's method slightly, but not by much; you can now use regular expressions as well:

select regexp_substr(:txt, '[^,]+', 1, level)
   from dual
 connect by regexp_substr(:txt, '[^,]+', 1, level) is not null

SQL提琴

这篇关于将varchar2解析为表(Oracle)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！