[etravels]

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??

[andrewst]

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:

select substr(address,1,instr(address,' ')-1) as address1

... and so on.

[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

Cheers
E

[ss659]

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?

[andrewst]

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.