在Oracle中解析管道定界的不规则字符串 [英] Parsing pipe delimited, irregular strings in Oracle

问题描述

我有数据和与此问题类似的问题:将管道定界的字符串解析为列?

I have data and a similar problem to this question: Parsing pipe delimited string into columns?

但是，我的数据可以包含没有数据的子字符串或不完整的字符串.请注意，最大为5个值.除非字符串具有5个值，否则上述问题中的解决方案将失败.见下文:

However my data can contain sub strings with no data, or strings that are incomplete. Note that 5 values is the maximum. The solution in the question above fails unless the string has 5 values. See below:

KEY         VALUE                               
----        -------------------
00          val1||||val5                                
01          val2|val2|val3|                        
02          val1|val2||val4

推荐答案

设置:

CREATE TABLE TABLE_NAME ( KEY, VALUE ) AS
SELECT '00',          'val1||||val5' FROM DUAL UNION ALL
SELECT '01',          'val2|val2|val3|' FROM DUAL UNION ALL
SELECT '02',          'val1|val2||val4' FROM DUAL;

查询1 :

SELECT  Key,
        REGEXP_SUBSTR( value, '([^|]*)(\||$)', 1, 1, NULL, 1 ) AS val1,
        REGEXP_SUBSTR( value, '([^|]*)(\||$)', 1, 2, NULL, 1 ) AS val2,
        REGEXP_SUBSTR( value, '([^|]*)(\||$)', 1, 3, NULL, 1 ) AS val3,
        REGEXP_SUBSTR( value, '([^|]*)(\||$)', 1, 4, NULL, 1 ) AS val4,
        REGEXP_SUBSTR( value, '([^|]*)(\||$)', 1, 5, NULL, 1 ) AS val5
FROM    table_name

结果:

KEY VAL1            VAL2            VAL3            VAL4            VAL5          
--- --------------- --------------- --------------- --------------- ---------------
00  val1                                                            val5            
01  val2            val2            val3                                            
02  val1            val2                            val4                            

这篇关于在Oracle中解析管道定界的不规则字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！