从oracle中的字符串创建列 [英] Making columns from a string in oracle

问题描述

我将区分值，并通过字符串列将它们设置为列. 我的字符串格式如下:

I'm going to distinguish values and make them columns through a string column. my string format is as below :

|| 1 | 21 | -1 | -1 | -1 | 0 ||||

||1|21|-1|-1|-1|0||||

我已经尝试过使用instr或regexp_instr，但是在某些行中，第一个字符为null，它返回错误的数据.此处的分隔符为'|'

I already have tried to using instr or regexp_instr but as in some rows the first character is null ,it returns wrong data.here the separator is '|' .

在此处输入图像描述

非常感谢您能帮助我解决这个问题.

It's appreciated if you could help me into this issue.

推荐答案

查询:

WITH sample_data ( "COMMENT" ) AS (
  SELECT '||1|21|-1|-1|-1|0||||' FROM DUAL
)
SELECT REGEXP_SUBSTR( "COMMENT", '(.*?)(\||$)', 1, 1, NULL, 1 ) AS col1,
       REGEXP_SUBSTR( "COMMENT", '(.*?)(\||$)', 1, 2, NULL, 1 ) AS col2,
       REGEXP_SUBSTR( "COMMENT", '(.*?)(\||$)', 1, 3, NULL, 1 ) AS col3,
       REGEXP_SUBSTR( "COMMENT", '(.*?)(\||$)', 1, 4, NULL, 1 ) AS col4,
       REGEXP_SUBSTR( "COMMENT", '(.*?)(\||$)', 1, 5, NULL, 1 ) AS col5,
       REGEXP_SUBSTR( "COMMENT", '(.*?)(\||$)', 1, 6, NULL, 1 ) AS col6,
       REGEXP_SUBSTR( "COMMENT", '(.*?)(\||$)', 1, 7, NULL, 1 ) AS col7,
       REGEXP_SUBSTR( "COMMENT", '(.*?)(\||$)', 1, 8, NULL, 1 ) AS col8,
       REGEXP_SUBSTR( "COMMENT", '(.*?)(\||$)', 1, 9, NULL, 1 ) AS col9,
       REGEXP_SUBSTR( "COMMENT", '(.*?)(\||$)', 1, 10, NULL, 1 ) AS col10,
       REGEXP_SUBSTR( "COMMENT", '(.*?)(\||$)', 1, 11, NULL, 1 ) AS col11,
       REGEXP_SUBSTR( "COMMENT", '(.*?)(\||$)', 1, 12, NULL, 1 ) AS col12
FROM   sample_data;

输出:

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10 COL11 COL12
---- ---- ---- ---- ---- ---- ---- ---- ---- ----- ----- -----
          1    21   -1   -1   -1   0

这篇关于从oracle中的字符串创建列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！