使用oracle按分隔符位置拆分字符串 [英] Split String by delimiter position using oracle

问题描述

我在oracle表中有一个字符串，例如',, defoifcd，87765'.意味着，前两个字符串为空.因此我需要在逗号分隔字符串作为Oracle中的定界符.我正在编写此查询

i have a string like ',,defoifcd,87765' in a oracle table.Means, first two strings are empty.so i need to Split the string by comma as delimiter in Oracle.i am writing this query

SELECT  
REGEXP_SUBSTR (TEST_STRING, '[^,]+', 1, 1)    AS part_1,
REGEXP_SUBSTR (TEST_STRING, '[^,]+', 1, 2)    AS part_2,
REGEXP_SUBSTR (TEST_STRING, '[^,]+', 1, 3)    AS part_3,
REGEXP_SUBSTR (TEST_STRING, '[^,]+', 1, 4)    AS part_4
FROM ABCD
;

这里ABCD是我的桌子，TEST_STRING是我的同伴.

here ABCD is my table and TEST_STRING is my coulmn.

但结果返回类似

PART_1,  part_2,  part_3,  part_4
defoifcd,87765

但是我想要这样的结果

PART_1,  part_2,  part_3,  part_4
                  defoifcd, 87765

意味着我需要在part_3和part_4列中使用'defoifcd'和'87765'值，因为前两个字符串为空，但是从我的查询来看，它在part_1和part_2列中是comimg.

means i need the 'defoifcd' and '87765' value in part_3 and part_4 column because first two strings are empty but from my query it is comimg in part_1 and part_2 column.

推荐答案

如果只有一个字符串，并且知道它总是正好有四个部分，则可以仅使用标准字符串函数(并避免使用正则表达式)来像这样拆分它，它们比较灵活，但通常速度较慢).

If you just have one string and you know it always has exactly four parts, you can split it like this, using only standard string functions (and avoiding regular expressions, which are more flexible but often slower).

注意:此答案的下半部分解决了可变数量部分"的字符串.

NOTE: The second half of this answer addresses strings with variable number of "parts".

with inputs ( str ) as (
       select ',,defoifcd,87765' from dual
     )
-- end of TEST data; SQL query begins below (use your actual table and column names)
select substr(str, 1, instr(str, ',') - 1) as part_1,
       substr(str, instr(str, ',') + 1, 
              instr(str, ',', 1, 2) - instr(str, ',') - 1) as part_2,
       substr(str, instr(str, ',', 1, 2) + 1, 
              instr(str, ',', 1, 3) - instr(str, ',', 1, 2) - 1) as part_3,
       substr(str, instr(str, ',', -1) + 1) as part_4
from   inputs;

PART_1   PART_2   PART_3   PART_4
-------- -------- -------- --------
                  defoifcd 87765

1 row selected.

如果不知道部件的数量，最好以其他格式获取输出(请参见下面的输出).如果需要将各部分安排在可以完成所有其他处理之后才能完成的列中-始终最好将其留给报表应用程序，而不是用SQL来完成.

If the number of parts is not known in advance, it is better to get the output in a different format (see the output below). If one needs to arrange the parts in columns that can be done after all other processing is done - and it is always best left to the reporting application rather than done in SQL anyway.

with inputs ( id, str ) as (
       select 1, ',,defoifcd,87765' from dual union all
       select 2, ''                 from dual union all
       select 3, 'a, b, c'          from dual
     )
-- end of TEST data; SQL query begins below (use your actual table and column names)
select id, str, level as part_number,
       substr(aug_str, instr(aug_str, ',', 1, level) + 1,
              instr(aug_str, ',', 1, level + 1) - instr(aug_str, ',', 1, level) - 1) as val
from   ( select id, str, ',' || str || ',' as aug_str from inputs)
connect by level <= length(str) - length(translate(str, 'z,', 'z')) + 1
       and prior id = id
       and prior sys_guid() is not null
;

ID STR              PART_NUMBER VAL
-- ---------------- ----------- ----------
 1 ,,defoifcd,87765           1
 1 ,,defoifcd,87765           2
 1 ,,defoifcd,87765           3 defoifcd
 1 ,,defoifcd,87765           4 87765
 2                            1
 3 a, b, c                    1 a
 3 a, b, c                    2  b
 3 a, b, c                    3  c

8 rows selected.

这篇关于使用oracle按分隔符位置拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！