Manipulating a string passed into a function

[dps]

Hello,

I have got a varchar string 07363403LO980011035206687030MCE/LOCE465/030
which I have passed into my function. I want it to be 'worked on' on that the function eventually returns the string as -
'061346(Time)03(RecType)SN01(PdtCode)0145(CampNo)035152198(SpCode)030(Sp_len)AMV/MAFR452/030(ComNum)'

All this is needed so to enable ease of reading of the strings inividual parts. For each one of these parts the data length will always be equal to the length given above; eg Time 061346 = 6 and PdtCode SN01 = 4 chars...etc.

Would I need to use something like

create or replace procedure p_format (p_string IN OUT varchar2)
is
--
BEGIN
p_string := SUBSTR(p_format,1,6)||
'Time'||SUBSTR(p_format,6,2)||
'RecType'||SUBSTR(p_format,8,4)||
'PdtCode'||SUBSTR(p_format,12,4)||.......
...so forth ???

Help would be very much appreciated

rgds

 

[SantaMufasa]

DPS,

This is how I would build the function (not a procedure):

create or replace function p_format (p_string IN varchar2) return varchar2
is
BEGIN
return SUBSTR(p_string,1,6)||
        '(Time)'||SUBSTR(p_string,6,2)||
         '(RecType)'||SUBSTR(p_string,8,4)||
          '(PdtCode)'||SUBSTR(p_string,12,4)||.......
end;
/

Notice that you can operate directly upon "p_string" (you had "p_format") and the code includes the parentheses ["("] surrounding your text descriptions.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:49 (23Jun04) UTC (aka "GMT" and "Zulu"), 10:49 (23Jun04) Mountain Time)

 

[dps]

Yes - thanks for that.

Yes I had p_format becasue I also had a function written down but for some reason chose to paste the procedure.

So I take it there is no other improvised manner in which this can be achievehd?

 

[SantaMufasa]

DPS,

There are always multiple methods by which to excoriate a feline . What do you mean by "improvised manner"? Certainly you could 1) execute the code in-line in your SQL instead of using a function, 2) create a procedure with an In/Out argument, and perhaps more methods. How many would you like?

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:03 (23Jun04) UTC (aka "GMT" and "Zulu"), 11:03 (23Jun04) Mountain Time)