OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Varrays

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Varrays [message #2207]

Thu, 27 June 2002 11:29

Lee
Messages: 56
Registered: May 1999

Member

Hi,

I have a variable field that can contain any sequence of numbers up to 10 such as:
field1 may equal '9','10','1'
or
field1 may equal '3','2','8','5' ect.

I need to insert a zero before before the numbers 1 - 9 to make:
field1 equal '09','10','01'
or
field1 equal '03','02','08','05'

I have tried to assign field1 to a VARRAY but it's not reading the numbers individually but as a whole. My code is as follows:

PROCEDURE the_name (field1 IN VARCHAR2)
IS

TYPE v_reg_array IS VARRAY(10) of VARCHAR2(100);
reg_array v_reg_array := v_reg_array(field1);
reg_list VARCHAR2(1000);

BEGIN

-- adding the prefix 0 to numbers that are <= 9
For i in 1..reg_array.COUNT LOOP
If reg_array(i) != '10' THEN
reg_array(i) := RTRIM(REPLACE(reg_array(i),'''','''0'),'0');
END IF;
reg_list := reg_list || ' ' || reg_array(i);
END LOOP;

Report message to a moderator

Re: Varrays [message #2208 is a reply to message #2207]

Thu, 27 June 2002 12:50

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Here's a pure SQL approach (this would work for data in a table or a variable):

sql>select c1, 
  2         replace(replace(replace(replace(replace(replace(replace(replace(replace(c1
  3                  , '''1''', '''01''')
  4                  , '''2''', '''02''')
  5                  , '''3''', '''03''')
  6                  , '''4''', '''04''')
  7                  , '''5''', '''05''')
  8                  , '''6''', '''06''')
  9                  , '''7''', '''07''')
 10                  , '''8''', '''08''')
 11                  , '''9''', '''09''') new_c1
 12    from t;
 
C1                             NEW_C1
------------------------------ ----------------------------------------
'9','10','1'                   '09','10','01'
'3','2','8','5'                '03','02','08','05'

Report message to a moderator

Previous Topic:	Re: insert into ... (select from ...) problem
Next Topic:	selecting,joining,concatenating, updating...all with one function or procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 05:17:38 CDT 2024