Re: Help-how to creat a serious of string using PL/SQL

You can use a FOR loop to call a concatenation function.

      DECLARE
         strTemp VARCHAR2(2048);
         intX    NUMBER;
      Begin
       FOR intX IN 1..26 LOOP
          strTemp := F_Add_A_String(strTemp, intX, intX);
       END LOOP;
      END;

Use a function that has an offset to find the alpha character - and a TO_CHAR(nn, 'format') to get the number string:

      FUNCTION F_Add_A_String(p_strSomething IN VARCHAR2,
                              p_x            IN NUMBER,
                              p_y            IN NUMBER)
      RETURN VARCHAR2 IS
       strTemp VARCHAR2(2048);
      BEGIN
                   -- add a comma and a space, but not the first time.
         IF LENGTH(p_strSomething) > 1 then
               strTemp := p_strSomething || ', ';
         ELSE
               strTemp := p_strSomething;
         END IF;
                  -- concat. character that's x away (offset) from 'A'
                  -- use "-1" to handle 'off by one' problem (A+1=B)
         strTemp := strTemp || UPPER(CHR(ASCII('A') - 1 + p_x ));

                   -- concatenate a string of two numeric characters.
                   -- SUSTR to avoid leading space character
         strTemp := strTemp || SUBSTR(TO_CHAR(p_y, '09'),2,2);

         --message('F_Add_A_String: ' || strTemp);
         --message('F_Add_A_String: ' || strTemp);
         RETURN strTemp;
      END;

If you wanted to use a FOR loop to retrieve the alpha character, I suppose you could start at "A", and FOR LOOP through the alphabet...

• setup the first letter of the alphabet strLoop := UPPER(CHR(ASCII('A')));
• sequence through the alphabet a number of times FOR intLoopCounter IN 1..(p_x - 1) LOOP strLoop := UPPER(CHR(ASCII(strLoop) + 1); END LOOP;

In article <7kcd6b$5td$1_at_nnrp1.deja.com>,   Johnson Chao <zjx110_at_my-deja.com> wrote:
> Hello;
> I am encountering a very easy seemed preblem, but I am confused.
>
> I just want to make a function to create a serious of string
> like below:
>
> A01,A02,...,A20
> B01,B02,...,B20
> ...
> H01,H02,...,H20
> My function is MFunc(x Number,y Number) return varchar2.
> i.e MFunc(1,3) should return A03
> MFunc(2,4) should return B04
>
> How can I use For..Loop to get it?
>
> Thanks ,very much.
>
> --
> Johnson Chao
> ctc Japan
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jun 18 1999 - 15:27:51 CEST