CODEWITHSUNDEEP
sqloracle-databaseplsql
Ruchita P
Ruchita P

Reputation: 389

Is there an alternate or better way to concatenate two strings

I have two strings, say "hello" and "world", I need to concatenate them as, first char from first string + first char from second string and so on ... e.g. "hweolrllod"

I tried with this...

Declare 
 string1 varchar2(10) := 'hello';
 string2 varchar2(10) := 'world'; 
 Type arr is varray(10) of varchar2(30);
 concat arr;
Begin
 concat := arr();
 concat.extend;
 for i in 1..5 loop
   concat(i) := substr(string1,i,1); 
   concat.extend;
   concat(i+1) := substr(string2,i,1);
   dbms_output.put_line(concat(i) || concat(i+1));
 end loop;
End;

Is there an alternate or better way to achieve this ?

Thank you,

Upvotes: 3

Views: 1001

Answers (4)

George Joseph
George Joseph

Reputation: 5922

Yes, if you are allowed to use straight sql, then using SQL would be a better option.

Eg:

I am using sys.odciVarchar2List to hold the first array and i am substring each character in that array using a connect by clause. ALso i am assiging rnk for first array as 1,3,5,7,9 etc..

For the second array i am doing similar logic and store the rnk value as 2,4,6,8 etc..

Finally i union all the two arrays and perform a aggregation query on the char_val ordered by rnk value

so we would have values as

rnk=1 h rnk=2 w rnk=3 hweolrllod

with first_array
  as (select  2*rownum-1 as rnk
             ,substr(column_value,rownum,1) as char_val
             ,column_value as col_val              
        from TABLE(sys.odciVarchar2List('hello'))
      connect by level<=length(column_value)
      )    
    ,second_array
    as (select  2*rownum as rnk
             ,substr(column_value,rownum,1) as char_val
             ,column_value as col_val              
        from TABLE(sys.odciVarchar2List('world'))
      connect by level<=length(column_value)
        )
select listagg(x.char_val,'') within group(order by x.rnk)
from (
 select rnk,char_val
   from first_array
 union all
 select rnk,char_val
   from second_array
     )x

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You may also use a SELECT query

with t(s1,s2) AS
(
select 'hello','world' from dual
)
select listagg(substr(s1,level,1)||substr(s2,level,1),'') 
   within group ( order by level) as col
FROM t connect by 
level <= length(s1);

If the strings are of different length, you could do level <= greatest(length(s1),length(s2))

Demo

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

If strings have the same length, then 

SQL> set serveroutput on
SQL> Declare
  2   string1 varchar2(10) := 'hello';
  3   string2 varchar2(10) := 'world';
  4   result  varchar2(20);
  5  begin
  6    for i in 1 .. length(string1) loop
  7       result := result || substr(string1, i, 1) || substr(string2, i, 1);
  8    end loop;
  9    dbms_output.put_line(result);
 10  end;
 11  /
hweolrllod

PL/SQL procedure successfully completed.

SQL>

Or, pure SQL (i.e. no PL/SQL):

SQL> with test as
  2    (select 'hello' s1, 'world' s2 from dual)
  3  select listagg(substr(s1, level, 1) || substr(s2, level, 1), '')
  4    within group (order by level) result
  5  from test
  6  connect by level <= length(s1);

RESULT
-----------------------------------------------------------------------------
hweolrllod

SQL>

Upvotes: 2

Дмитрий
Дмитрий

Reputation: 111

I don't understand your goal but i think it is the same as syntax sugar and anyway for me it alternative way

Declare 
 string1 varchar2(10) := 'hello';
 string2 varchar2(10) := 'world'; 
 Type arr is varray(10) of varchar2(30);
 concat arr;
 string3 varchar2(50);
Begin
 concat := arr();
 concat.extend;
 for i in 1..5 loop
/*   concat(i) := substr(string1,i,1); 
   concat.extend;
   concat(i+1) := substr(string2,i,1);
   dbms_output.put_line(concat(i) || concat(i+1));*/

   string3 := string3 ||substr(string1,i,1) || substr(string2,i,1);
   dbms_output.put_line(string3);
 end loop;

End;

Upvotes: 3

Related Questions