0

Oracle SQL - Looking to get comma separated substrings into rows **without using CONNECT BY clause **

Example

input varchar2 string is = 'AAA,BBB,CCC,DDD'

Expecting output as below

AAA BBB CCC DDD

Jonas Metzler
  • 4,517
  • 1
  • 5
  • 17
mangesh5171
  • 49
  • 1
  • 1
    Please explain why do you try to avoid `connect by`? It sounds the same as *how to filter rows without`where` clause* – astentx Jul 12 '23 at 05:49

2 Answers2

1

If you compare

SQL> with
  2  test (col) as
  3    -- sample string
  4    (select 'AAA,BBB,CCC,DDD' from dual)
  5  select regexp_substr(col, '[^,]+', 1, level) val
  6  from test
  7  connect by level <= regexp_count(col, ',') + 1;

VAL
------------------------------------------------------------
AAA
BBB
CCC
DDD

SQL>

to code which doesn't use connect by clause

SQL> with
  2  test (col) as
  3    -- sample string
  4    (select 'AAA,BBB,CCC,DDD' from dual),
  5  comma (col) as
  6    -- add comma AFTER the COL string so that code that follows is simpler to maintain
  7    (select col || ',' from test),
  8  temp (val, str) as
  9    -- recursion
 10    (select substr (col, 1, instr (col, ',') - 1) as val,
 11            substr (col, instr (col, ',') + 1) as str
 12     from comma
 13     where col like '%,%'
 14     --
 15     union all
 16     --
 17     select substr (str, 1, instr (str, ',') - 1),
 18            substr (str, instr (str, ',') + 1)
 19     from temp
 20     where str like '%,%'
 21    )
 22  select val
 23  from temp
 24  where val is not null;

VAL
--------------------------------------------------------------------------------
AAA
BBB
CCC
DDD

SQL>

the question is: why would you want to do that?

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • "why would you want to do that?" Shorter code is not always more efficient code. I'm not sure that your recursive query is more efficient (as it rebuilds the sub-string multiple times) but there are [alternative ways of solving the problem](https://stackoverflow.com/a/38372145/1509264) that mean that recursive queries may be more efficient than hierarchical queries (even if the recursive query is more to type). – MT0 Jul 12 '23 at 08:06
0

You can use:

WITH bounds ( value, start_pos, end_pos ) AS (
  SELECT value,
         1,
         INSTR( value, ',' )
  FROM   table_name
UNION ALL
  SELECT value,
         end_pos + 1,
         INSTR( value, ',', end_pos + 1 )
  FROM   bounds
  WHERE  end_pos > 0
)
SELECT CASE end_pos
       WHEN 0
       THEN SUBSTR(value, start_pos )
       ELSE SUBSTR(value, start_pos, end_pos - start_pos )
       END AS value
FROM   bounds;

At each recursion, you are only finding the position of the next comma in the string and you start looking from the position after the previous comma which:

  1. does not use (slow) regular expressions;
  2. does not repeatedly count delimiters from the beginning of the string (so you do not have to repeat work at each recursion); and
  3. does not perform string manipulations until the final non-recursive part of the query.

So, according to the analysis performed in this question, it may be faster than using hierarchical queries.

Which for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'AAA,BBB,CCC,DDD' FROM DUAL;

Outputs:

VALUE
AAA
BBB
CCC
DDD

fiddle

MT0
  • 143,790
  • 11
  • 59
  • 117