Splitting up a string 2

[borisbe]

I have an address field that I want to split up to 4 fields. I can split out the 1st part but not sure how to do the rest.

Addresses

3347 E Michelson Dr
140 W 200 N

For Address_1, I used "substr(ADDRESS,1,(instr(ADDRESS,' ')) - 1)

Address_1, Address_2, Address_3, Address_4

3347, E, Michelson, Dr
140, W, 200 S

I only want Address_4 if the last part of the address includes certain characters Dr, Ave, Rd, Road, etc. after the last space in the field so that is why Dr is in the Address_4 field for the 1st address and nothing for the 2nd address.

Thank you for any help you can give me.

 

[carp]

And how will you want to handle addresses like
123 Martin Luther King Blvd ?

 

[borisbe]

Good question. If the characters between the 1st space and 2nd space doesn't include E, W, S, N, East, West, South, North then have the Address_2 blank so:

Address_1, Address_2, Address_3, Address_4

3347, E, Michelson, Dr
140, W, 200 S
123, ,Martin Luther King, Blvd (Blvd is also in the list that I would designate that if the last part of the address includes it then have it in the Address_4 field)

Thanks for the help. I really appreciate it.

 

[carp]

I think your best bet is going to be to write a stored function. As arguments, it could accept the full address and the position you want, then return the part you are seeking. This way you could also throw in tests for incomplete addresses (e.g., "123 Martin Luther King" - since there is no indicator of Blvd, Ave, St, etc, you could throw an error if that was what you wanted to do.
Also, you would be able to use the function in a SQL statement or in calls from within other functions/procedures. While you could do this all from within a single SQL statement, it would be very ugly by the time you got all of the contingencies covered!

 

[SantaMufasa]

Borisbe,

As I'm sure you are aware, making addresses behave in a uniform manner can be very complicated, especially if the source of the address data entry is outside your organization's control. There is an entire industry that has arisen to deal with address uniformity. A site that I can recommend, which has software (both free trial and purchasable), is Melissa Data. They specialize in software that handles physical addresses, even of an international nature.

If your current need is informal or one-time in nature (with fairly consistent, well-behaved addresses), then you can produce in-house SQL code to take care of business.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

BTW, Borisbe, one of your addresses leads me to believe that you might be from Utah. If so, I have SQL code that I wrote to both produce reverse street directories and distance-between functions for Utah addresses. Let me know if you have a need or interest there.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[borisbe]

I wanted to compare certain portions of the address to an excel file using a reporting tool so I think I might just split up the address in the reporting tool since I know someone can help me here and it should be easy. Thanks again everyone.