| Basic Question [message #71] |
Thu, 10 January 2002 10:51  |
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  |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
