Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    93

    Talking Unanswered: Creating a function to weed out the crlf on a address fields

    I want to set up a function to take out the crlf for an address field into address 1,address 2, address3, address4 instead of using the following which return a some 650 records that has more than 4 address lines...

    Decode statement, is not working so am suggested to use a function?

    SELECT CONTACT_SOURCE.PARTY_ID+10000000,

    decode(( (INSTR(Known.Address, CHR(13))) ),0,( Known.Address ),SUBSTR(Known.Address,1,( (INSTR(Known.Address, CHR(13)))

    )-1)),

    decode(( (INSTR(Known.Address, CHR(13))) ),0,'',decode(( decode(( (INSTR(Known.Address, CHR(13))) ),0,0,

    (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address, CHR(13))) )+2), CHR(13)))) ),0,SUBSTR(Known.Address,( (INSTR(Known.Address,

    CHR(13))) )+2),substr(Known.Address,( (INSTR(Known.Address, CHR(13))) )+2,( decode(( (INSTR(Known.Address, CHR(13))) ),0,0,

    (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address, CHR(13))) )+2), CHR(13)))) )))),

    decode(( decode(( (INSTR(Known.Address, CHR(13))) ),0,0, (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address, CHR(13))) )+2),

    CHR(13)))) ),0,'',decode(( decode(( decode(( (INSTR(Known.Address, CHR(13))) ),0,0, (INSTR(SUBSTR(Known.Address,(

    (INSTR(Known.Address, CHR(13))) )+2), CHR(13)))) ),0,0, (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address, CHR(13))) )+(

    decode(( (INSTR(Known.Address, CHR(13))) ),0,0, (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address, CHR(13))) )+2), CHR(13))))

    )+3), CHR(13)))) ),0,SUBSTR(Known.Address,( decode(( (INSTR(Known.Address, CHR(13))) ),0,0, (INSTR(SUBSTR(Known.Address,(

    (INSTR(Known.Address, CHR(13))) )+2), CHR(13)))) )+( (INSTR(Known.Address, CHR(13))) )+3),substr(Known.Address,( decode((

    (INSTR(Known.Address, CHR(13))) ),0,0, (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address, CHR(13))) )+2), CHR(13)))) )+(

    (INSTR(Known.Address, CHR(13))) )+3,( decode(( decode(( (INSTR(Known.Address, CHR(13))) ),0,0, (INSTR(SUBSTR(Known.Address,(

    (INSTR(Known.Address, CHR(13))) )+2), CHR(13)))) ),0,0, (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address, CHR(13))) )+(

    decode(( (INSTR(Known.Address, CHR(13))) ),0,0, (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address, CHR(13))) )+2), CHR(13))))

    )+3), CHR(13)))) )))),

    decode(( decode(( decode(( (INSTR(Known.Address, CHR(13))) ),0,0, (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address,

    CHR(13))) )+2), CHR(13)))) ),0,0, (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address, CHR(13))) )+( decode((

    (INSTR(Known.Address, CHR(13))) ),0,0, (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address, CHR(13))) )+2), CHR(13)))) )+3),

    CHR(13)))) ),0,'',substr(Known.Address,( (INSTR(Known.Address, CHR(13))) )+( decode(( (INSTR(Known.Address, CHR(13))) ),0,0,

    (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address, CHR(13))) )+2), CHR(13)))) )+( decode(( decode(( (INSTR(Known.Address,

    CHR(13))) ),0,0, (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address, CHR(13))) )+2), CHR(13)))) ),0,0,

    (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address, CHR(13))) )+( decode(( (INSTR(Known.Address, CHR(13))) ),0,0,

    (INSTR(SUBSTR(Known.Address,( (INSTR(Known.Address, CHR(13))) )+2), CHR(13)))) )+3), CHR(13)))) )+4)),

    A function would be easier? any suggestions?

    btw...the address currently is as follows:
    Address
    84Hamilton road||southwimbledon||Merton Road||london||london

    to become
    Address1 Address2 Address3 Address4
    84 Hamilton road|south wimbledon|Merton Raod|london


    Any ideas?

    Cheers
    Cheers
    Etravels

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You could take a look at this posting of mine on Parsing delimited fields. It would need a little work to adapt it to your requirement - like writing a wrapper function where you pass in a string it returns a number of values (obtained using my package).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •