Home » Developer & Programmer » Reports & Discoverer » Parse a string and convert the text
Parse a string and convert the text [message #212551] Fri, 05 January 2007 16:10 Go to next message
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 #212587 is a reply to message #212551] Sat, 06 January 2007 01:37 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Check this DBMS_UTILITY.COMMA_TO_TABLE topic on this Forum - perhaps you might use this idea.

Furthermore, Tom Kyte has an example of the str2tab function which would "convert" your string into a nested table.

See which one (if any) solution suits better for your needs.
Re: Parse a string and convert the text [message #213169 is a reply to message #212587] Tue, 09 January 2007 13:01 Go to previous messageGo to next message
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

Re: Parse a string and convert the text [message #213172 is a reply to message #213169] Tue, 09 January 2007 13:19 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you for sharing the solution with us!
Previous Topic: Interview questions
Next Topic: group above report on HIERARCHY data
Goto Forum:
  


Current Time: Sun Dec 04 14:51:48 CST 2016

Total time taken to generate the page: 0.16417 seconds