如何使用逗号分隔的字符串和逗号之间的(可能)空白字段按顺序拆分字符串 [英] How to split a string in order with a comma-separated string and (possible) empty fields between commas

问题描述

我需要在Oracle PL/SQL中以逗号分隔的字符串顺序分割，包括空字段作为无"值. 这是一个包含6个字段的示例，但可以包含更多或更少的字段

I need to split a string in Oracle PL/SQL with comma-separated in order, including empty fields as "none" value. This is an example with 6 fields but can have more or less fields

line varchar2(100) := "value1;value2;;;value5;value6;";
WITH test AS (SELECT line FROM DUAL)
  SELECT NVL(REGEXP_SUBSTR (line, '[^;]+', 1, ROWNUM),'none') as SPLIT
    FROM test
  CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (line, '[^;]+'));

输出:

 value1      
 value2      
 value5      
 value6      
 none         
 none     

理想的输出:

value1      
value2  
none      
none  
value5      
value6      

推荐答案

with test as (select 'value1;value2;;;value5;value6;' line from dual)
select nvl(regexp_substr (line, '([^;]*);', 1, rownum, null, 1), 'none') as split
from test
connect by level <= regexp_count (line, ';');

SPLIT                         
------------------------------
value1
value2
none
none
value5
value6

6 rows selected. 

这篇关于如何使用逗号分隔的字符串和逗号之间的(可能)空白字段按顺序拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！