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

    Unanswered: taking out the concatenated fields into one?

    I have the following:



    Address field which is concatenated.

    3 The Manse||Tantany Lane





    Now I want to select from the address field so I can break down the ‘address’ field into address1. address2, address3



    Anyideas?


    Select address??
    Cheers
    Etravels

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You will use INSTR and SUBSTR to parse the concatenated address. However, you will need to define some rules. For example, does address1 always end at the first space? If yes then:
    Code:
    select substr(address,1,instr(address,' ')-1) as address1
    ... and so on.

  3. #3
    Join Date
    Feb 2004
    Posts
    93

    Red face thanx

    first reply is yes
    and how would I take out the address2 and address3 from the one address?
    Also some addresses will be nULL values

    Cheers
    E
    Cheers
    Etravels

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    What is address 1,2,3 for 3 The Manse||Tantany Lane?

    Andrew makes a good point - how do you know where to break an address?
    Oracle OCPI (Certified Practicing Idiot)

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by etravels
    first reply is yes
    and how would I take out the address2 and address3 from the one address?
    Also some addresses will be nULL values
    I've show how INSTR can be used to locate a delimiter string (e.g. ' ' or '||') in the address, and also how SUBSTR can be used to take out part of the address between delimiters. Getting address2 is similar to getting address1, except that you start after the first space, and go up to the '||' delimiter. You need to experiment with using these functions yourself.

Posting Permissions

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