| Parse a string and convert the text [message #212551] |
Fri, 05 January 2007 16:10  |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
This is tricky , I have a field in a report that contains a string that looks something like this 'A001, D002, F003',
each one is a code. I need to replace the code with its description so the field should look like this once converted 'Apple, Dog, Frog' any suggestions on how I can do this in a report? The string is saved in one table and each code and its description is saved in another table.
|
|
|
|
|
|
| Re: Parse a string and convert the text [message #213169 is a reply to message #212587] |
Tue, 09 January 2007 13:01  |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
Thanks for the help using the above function, I created the following function in report builder program units and called it from a field trigger. Its giving the results I need. Thanks.
function read_string return Varchar2
IS
line_tab_t1 dbms_utility.uncl_array;
len pls_integer;
cnt pls_integer:=1;
error_status integer;
p_stream varchar2(550);
p_string varchar2(250);
CURSOR Desc_cur is
select distinct c.new_code, c.new_description
from codes c
where c.waste_code = ltrim(rtrim(p_stream));
Desc_rec Desc_cur%rowtype;
Begin
DBMS_UTILITY.COMMA_TO_TABLE(:NEW_STRING,len,line_tab_t1);
--string looks like this (d001, f001, b009)
for i in 1..line_tab_t1.count loop
exit when cnt > len;
p_stream:= line_tab_t1(i);
open desc_cur;
fetch desc_cur into desc_rec;
if desc_cur%notfound then
null;
end if;
p_string:= p_string||', '||desc_rec.new_description;
cnt := cnt + 1;
close desc_cur;
end loop;
P_STRING := SUBSTR(P_STRING,2); --remove beginning comma
return(p_string);
EXCEPTION
WHEN others THEN
RETURN('Description Not Available');
End;
[Updated on: Tue, 09 January 2007 13:03] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
