Split String by delimiter position using oracle

Question

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
;

here ABCD is my table and TEST_STRING is my coulmn.

but result returning like

PART_1,  part_2,  part_3,  part_4
defoifcd,87765

But i want the result like,

PART_1,  part_2,  part_3,  part_4
                  defoifcd, 87765

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.

Possible duplicate of [Split comma separated values to columns in Oracle](http://stackoverflow.com/questions/31464275/split-comma-separated-values-to-columns-in-oracle) — Thomas G, Jan 30 '17 at 16:31
To be very specific: dont check the accepted answer but the one from Gary: he is explaining and fixing your current issue. — Thomas G, Jan 30 '17 at 16:32
Then you will not be able to have the columns lined up like that. Unless you use "dynamic SQL" (which is a different process, and often not optimal) the number of output columns must be known ahead of time, before looking at the data. Is there a maximum number of parts that is known beforehand and can be used for the query? If not, do you really need the output in columns (instead of a column for part designation and another column for the value)? — , Jan 30 '17 at 16:37
Regarding the link provided by Thomas: two observations. First, that thread does not show how the splitting can be done with regular string functions, it only shows REGEXP based solutions. Those can be significantly slower than standard string functions. Second, if more than one string needs to be split, that thread doesn't show how to do that (with hierarchical queries, or as an alternative with a recursive query, etc.) — , Jan 30 '17 at 17:07

score 1 · Answer 1 · answered Jan 30 '17 at 17:04

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.

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.

Your first example was just what I was going through; your second example is very creative ;) — BobC, Jan 30 '17 at 18:04

Split String by delimiter position using oracle

1 Answers1