PL/SQL String Manipulation
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;
- address is a varchar2(250) which has to be split into 4 lines al1,al2,al3,al4 of varchar2(100) each.
- address contains newline characters CHR(10). And based on these the splitup has to be done.
- 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.
- only if the address length is more than 100 the splitup has to be done.
- 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 r开发者_StackOverflow社区est of the line is added to the second address line
- 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
- 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
- 'door #nn xxxxxxxxxx XXXXXXXXXX yyyy' ->length 100 max
- 'YYYYYYYYYYY Zip-NNNNNNN, zzzzzzzzzzzz' ->length 100 max
- 'zzzzzzzzzzz, ZZZZZZZZZZZZZZZZZZZZZ' ->length 100 max
- '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.
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?
精彩评论