query to split address column

[homer.favenir]

hi to all,
can anyone please tell me how to split 1 column and sort it by splitted column.

i have 1 table with address column. The address column contains strt # and strt name.
e.g.

ADDRESS
123 main st. 
123 10th avenue.

because i need to sort first the strt name before the strt # and before the other columns.

can anyone please....


thanks

 

[llkhoutx]

This issue is fraught with many problems. An address may be a P.O. Box or it may not have a number.

You can check for the left most space/blank character, everything to the right may be the street, but them it may not be too. The instr function will give the position of the first space/blank character.

A better way is to have a separate field for the number, but users will screw that up too.

 

[Banana]

better way is to have a separate field for the number, but users will screw that up too.

While having separate fields for house number, street name, apt/suite #, city, state and zip is usually sufficient, you could be even anal-retentive and go a step further. For example, you could force users to specify if this address is a house address or a PO Box and adjust your form to receive the correct input.