Home » SQL & PL/SQL » SQL & PL/SQL » Basic Question
Basic Question [message #71] Thu, 10 January 2002 10:51 Go to next message
jj
Messages: 11
Registered: July 2001
Junior Member
Hi,

Basically I have an input varchar2 variable - Region. This variable will contain a series of 2 digit numbers separated by commas, ie...02,01,06,10 or 03 etc.

Without creating an Oracle table, I need to output the variables with the word Region(s) in front of it ie
Regions 2,1,6,10 or Region 3

If someone could point me in the right direction it would be greatly appreciated.

Thanks
Re: Basic Question [message #73 is a reply to message #71] Thu, 10 January 2002 12:09 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
1 declare
2 var1 varchar2(50):='02,03,04 or 05';
3 begin
4 var1:='Regions '||replace('02,03,04 or 05','or','or Region');
5 dbms_output.put_line(var1);
6* end;
SQL> /
Regions 02,03,04 or Region 05
Re: Basic Question [message #75 is a reply to message #71] Thu, 10 January 2002 12:55 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
yes, create function using pl/sql table (array).

here is code

create or replace function reg(pstrval varchar2) return varchar2 IS
TYPE tab1 is table of number index by binary_integer;
region tab1;
mainstr VARCHAR2(40) := pstrval;
splitstr VARCHAR2(30) := '';
l_count NUMBER(20) := 1;
itr_count NUMBER(20) := 0;
processed BOOLEAN := FALSE;
ret_reg VARCHAR2(60):='';
begin

itr_count := 0;
processed := FALSE;
LOOP
itr_count := itr_count+1;
IF instr(mainstr,',',1,itr_count)>0 THEN
splitstr := SUBSTR(mainstr,l_count,(INSTR(mainstr,',',1,itr_count)-l_count));
l_count := INSTR(mainstr,',',1,itr_count)+1;
ELSE
splitstr := SUBSTR(mainstr,l_count,LENGTH(mainstr)+1-l_count);
processed := TRUE;
END IF;

region(itr_count):=to_number(splitstr);
IF processed THEN
EXIT ;
END IF;
END LOOP;
FOR i in 1..region.count LOOP
if i=1 then
ret_reg := region(i);
ELSE
ret_reg:=ret_reg||','||region(i);
END IF;
END LOOP;
IF region.count()=1 then
ret_reg := 'Region '||ret_reg;
ELSIF region.count()>1 then
ret_reg := 'Regions '||ret_reg;
END IF;
return ret_reg;
end;

Testing function;;

SQL> select reg('01,02,11,12') region from dual;

REGION
---------------------------------------------------------------
Regions 1,2,11,12

SQL> select reg('01') from dual;

REG('01')
-----------------------------------
Region 1
Previous Topic: utl_file and parsing a file
Next Topic: 'unique constraint violation' (ORA-00001) at an INSERT
Goto Forum:
  


Current Time: Tue Mar 31 12:25:50 CDT 2020