title: PL/SQL String Manipulation
body: <p>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:</p>
  
  <p>create or replace procedure split_address
  (
  address in varchar2,
  al1 out varchar2,
  al2 out varchar2,
  al3 out varchar2,
  al4 out varchar2
  )
  is.... end;</p>
  
  <ol>
  <li>address is a varchar2(250) which has to be split into 4 lines al1,al2,al3,al4 of varchar2(100) each.</li>
  <li>address contains newline characters CHR(10). And based on these the splitup has to be done.</li>
  <li>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.</li>
  <li>only if the address length is more than 100 the splitup has to be done.</li>
  <li>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</li>
  <li>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</li>
  <li>address line 3 is a mandatory output.</li>
  </ol>
  
  <p>Let me give an example. Let the address be</p>
  
  <p>'door #nn<br>
  xxxxxxxxxx<br>
  XXXXXXXXXXX<br>
  yyyy<br>
  YYYYYYYYY<br>
  Zip-NNNNNN<br>
  zzzzzzzzzzzzzzz<br>
  ZZZZZZZZZZZZZZZZZZZZ'<br></p>
  
  <p>If the total length of this is &lt; 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</p>
  
  <ol>
  <li>'door #nn xxxxxxxxxx XXXXXXXXXX yyyy' ->length  100 max</li>
  <li>'YYYYYYYYYYY Zip-NNNNNNN, zzzzzzzzzzzz' ->length  100 max</li>
  <li>'zzzzzzzzzzz, ZZZZZZZZZZZZZZZZZZZZZ' ->length 100 max</li>
  <li>'ZZZZZZ' -> length 100 max</li>
  </ol>
  
  <p>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.</p>