使用预定义的系统函数将定界字符串(或列)转换为Oracle中的行 [英] Converting a delimited string (or column) to rows in Oracle using a pre-defined system function

问题描述

几年前，在Oracle 10或9中，我使用了一个类似于"DBMS_COL_2_VAL"的函数(这绝对不是正确的函数).

此内置函数的目的是基于指定的定界符将提供的字符串转换为行.我知道有多种方法可以将定界的字符串转换为行，但是我要寻找的是此特定功能.

如果您能帮上忙，那就太好了.

请勿使用CONNECT，CASE或REGEX提供任何解决方案.

谢谢

解决方案

我要查找的功能是SYS.DBMS_DEBUG_VC2COLL.

从技术上讲，它不会将定界字符串转换为列，而是将以逗号分隔的值列表转换为行.我意识到，在找到旧的帖子之后.. >

示例代码和结果:

with test as  (
    select column_value AS c1  
      from table( SYS.DBMS_DEBUG_VC2COLL( 'a','b','c' ) )  
   )  
 select * from test;

结果:

c1   
__  
a    
b    
c    

A few years ago in Oracle 10 or 9 I used a function which was something like "DBMS_COL_2_VAL" (This is definitely not the right function).

The purpose of this built in function is to convert the provided string to rows based on a specified delimiter. I know that there are multiple ways of converting a delimited string to rows, but what I looking for is this specific function.

If you can help that would be great.

Please do not provide any solutions with CONNECT, CASE, or REGEX.

Thanks

解决方案

The function I was trying to find was SYS.DBMS_DEBUG_VC2COLL.

Technically speaking it does not convert a delimited string to column, but it converts a list of comma separated values to rows. I realized that after I found an old post.

Sample code and results:

with test as  (
    select column_value AS c1  
      from table( SYS.DBMS_DEBUG_VC2COLL( 'a','b','c' ) )  
   )  
 select * from test;

Result:

c1   
__  
a    
b    
c    

这篇关于使用预定义的系统函数将定界字符串(或列)转换为Oracle中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！