Re: SQL*PLUS Syntax for Concatenation

From: Haakon T. Soenderland <hts_at_sasdata.no>
Date: 1995/08/23
Message-ID: <41f8t3$eee_at_news.dax.net>#1/1

In article <dschellenberg.6.0011DCDA_at_man.net>, dschellenberg_at_man.net says... [snip]
>Question 1:
>
> I have two tables, call them table1 and table2. I want something like this:
>
> UPDATE TABLE1
> SET TABLE1.COL2 = concatenated CHARs from TABLE2.COL2
> WHERE TABLE2.ID = TABLE1.ID;
>
> In other words, I just want to concatenate all of the strings from
>table2.column2, separating each with a space, and then set a much bigger CHAR
>in table1 to that string. I have no idea!

PL/SQL: DECLARE

   v_col2table1  table1.col2%TYPE;
   v_col2table2  table2.col2%TYPE;
   v_length number

BEGIN      select col2 into v_col2table1 from table1    where id = <what you want (one row only!)>;

  v_length := length(v_col2table1);
  v_col2table2 := substr(v_col2table1,1,1)||' ';   for i in 2..v_length loop
    v_col2table2 := v_col2table2||substr(v_col2table1,i,1)||' ';   end loop;   

  update table2

     set col2 = v_col2table2
   where id = <id from table1>;

  commit;
END; This is straight from memory and not tested, but I think you should be able to get it to work. This works for one row in table1, if you need to get more than one row you would have to loop on table1 using a cursor. Have a look in the PL/SQL manual.

Hope this helps,
Haakon

-- 
hts_at_sasdata.no | haakon.soenderland_at_thcave.bbs.no | Haakon T. Soenderland
Scandinavian Airlines Data Norway A/S  
---
"40 skiver og et herpa anlegg, en leilighet som trenger aa spyles. Ingen 
 venner og ingen penger, alt jeg har er mine klamme hender.." Jokke '94
Windows 95: A another first from Microsoft!
The first OS to be obsolete *before* it was released.

Received on Wed Aug 23 1995 - 00:00:00 CEST