PL/SQL String Manipulation

Question

I need to split an address which is a single string into multiple strings. Below are the requirements to for the pl/sql procedure that splits up the address into the corresponding address lines. The parameters of the procedure are:

create or replace procedure split_address ( address in varchar2, al1 out varchar2, al2 out varchar2, al3 out varchar2, al4 out varchar2 ) is.... end;

1. address is a varchar2(250) which has to be split into 4 lines al1,al2,al3,al4 of varchar2(100) each.
2. address contains newline characters CHR(10). And based on these the splitup has to be done.
3. if address's length is less than or equal to 100 then the whole address needs to be put into the 3rd address line al3.
4. only if the address length is more than 100 the splitup has to be done.
5. if one line is more than 100 characters then the line has to be split up into two by seperator ',' in the middle or near the 50th position and rest of the line is added to the second address line
6. If the number of lines are more than 4 then merge each segment into one address line to a max of 100 chars upto 4 segments
7. address line 3 is a mandatory output.

Let me give an example. Let the address be

'door #nn
xxxxxxxxxx
XXXXXXXXXXX
yyyy
YYYYYYYYY
Zip-NNNNNN
zzzzzzzzzzzzzzz
ZZZZZZZZZZZZZZZZZZZZ'

If the total length of this is < 100 then it must be put into al3 directly no splitting up. If the address length is more than 100 then the problem comes. This address has 8 lines but must be made into 4 lines like

1. 'door #nn xxxxxxxxxx XXXXXXXXXX yyyy' ->length 100 max
2. 'YYYYYYYYYYY Zip-NNNNNNN, zzzzzzzzzzzz' ->length 100 max
3. 'zzzzzzzzzzz, ZZZZZZZZZZZZZZZZZZZZZ' ->length 100 max
4. 'ZZZZZZ' -> length 100 max

The system is oracle-10g, I can use its features. The problem is simple but getting the logic into the code is a bit difficult. Please provide some tips. Thanks in advance.

Answer 1

I think a function might be a better idea than a procedure.

Other observations:

• use the oracle instr function to search for newlines and commas
• use the oracle length function to determine whether individual fields exceed 100 characters
• are you sure that all possible inputs can produce outputs that will match the requirements specified? For example, what if you have a single input line of more than 100 characters, that only contains one newline character?