How to concatenate strings in oracle pl/sql loop?

Question

I am getting a string of data from application in the variable Pvalue1= a;b;c;e%;f%;h%; which is input from an excel. In my pl/sql code I want to store them in two different variables like this

v1= 'a';'b';'c'; v2= e%f%h%

I am using the below code but this does not seem to be working as expected.

    FOR i in 1..FileRowCount LOOP
     if instr(v1,'%')=0 then 
     v1 := SUBSTR(Pvalue1,0,INSTR(Pvalue1, ';',1,1)-1)||v1
     elsif instr(v1,'%')<>0 then
     v2 := SUBSTR(Pvalue1,0,INSTR(Pvalue1, ';',1,1)-1)||v2
     end if;
     Pvalue1 := SUBSTR(Pvalue1,INSTR(Pvalue1, ';',1,1)+1);
    end loop;

Barbaros Özhan · Accepted Answer · 2021-02-24 15:50:02Z

You can consecutively use REGEXP_SUBSTR() (in order to determine each substring delimited by semi-colons), and INSTR() (in order to determine the substrings with or without % character). Then combine the string trough use of LISTAGG() such as

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
 Pvalue1 VARCHAR2(100):='a;b;c;e%;f%;h%;';
 v1      VARCHAR2(100);
 v2      VARCHAR2(100);
BEGIN
  WITH t AS
  (
  SELECT REGEXP_SUBSTR( Pvalue1, '[^;]+', 1, level ) AS piece, level AS lvl        
    FROM dual
  CONNECT BY level <= REGEXP_COUNT( Pvalue1, ';' ) 
  )
  SELECT LISTAGG(CASE WHEN INSTR( piece,'%')=0 THEN ''''||piece||'''' END,';') WITHIN GROUP (ORDER BY lvl) AS v1,
         LISTAGG(CASE WHEN INSTR( piece,'%')>0 THEN piece END) WITHIN GROUP (ORDER BY lvl) AS v2
         INTO v1, v2
    FROM t; 
  DBMS_OUTPUT.PUT_LINE( 'v1 : '||v1 );
  DBMS_OUTPUT.PUT_LINE( 'v2 : '||v2 );      
END; 
/

v1 : 'a';'b';'c'
v2 : e%f%h%

Collectives™ on Stack Overflow

How to concatenate strings in oracle pl/sql loop?

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