PL SQL - How to loop results of splitting a csv string?

Question

So I have a string

test = 'a,b,c,d';

I want to split this string up into each word deliminated by a comma. Then I want to loop the results.

I have seen this solution:

WITH t AS
( SELECT 'a,b,c,d,e' AS txt FROM dual
)
SELECT CAST(REGEXP_SUBSTR (txt, '[^,]+', 1, level) AS CHAR(12))
FROM t
CONNECT BY level <= LENGTH(regexp_replace(txt,'[^,]*'))+1;

However, it does not allow me to loop the result set. I have additional processing that needs to be done with each substring.

How can I go about looping this result set?

You can loop over that result set just as you can with any other. Why do you think you can't loop over it? If you have code that isn't working for some reason then add that to the question, along with whatever error you get. — Alex Poole
– Alex Poole, Commented Jun 13, 2016 at 18:22

MT0 · Accepted Answer · 2016-06-13 19:02:52Z

You can just use an inline view with your query:

WITH t AS (
  SELECT 'a,b,c,d,e' AS txt FROM dual
)
SELECT extra_processing( value )
FROM   (
  SELECT CAST(REGEXP_SUBSTR (txt, '[^,]+', 1, level) AS CHAR(12)) AS value
  FROM t
  CONNECT BY level <= LENGTH(regexp_replace(txt,'[^,]*'))+1
);

Since this is tagged with PL/SQL - why use SQL at all:

Oracle Setup:

A small helper function to split a string on a delimiter:

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC
AS
  p_result       SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

Code:

DECLARE
  list SYS.ODCIVARCHAR2LIST := split_String( 'a,b,c,d,e' );
BEGIN
  FOR i IN 1 .. list.LAST LOOP
    -- do your extra processing
  END LOOP;
END;
/

or you can just do it in SQL:

SELECT extra_processing( COLUMN_VALUE )
FROM   TABLE( split_String( 'a,b,c,d,e' ) )

(Note: COLUMN_VALUE is the column name Oracle gives to values generated by the TABLE operator.)

Collectives™ on Stack Overflow

PL SQL - How to loop results of splitting a csv string?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related