Separate data from one column to many

[Idokyoku2]

Hello All,
I'd like to take a table provided by a customer that has four columns provided as one like so:
Business_Addr
1123 Main st Los Angeles CA 90057

and split them out into different columns:
Addr1 City State Zip
1123 Main St Los Angeles CA 90057

Not sure exactly how to go about it.

Tried something like;
Insert into Active02-02 (BusAddr1)
Select Substring(RTRIM(Business_Addr,1,' '))
From Active02;

Only I receive an Error
The RTRIM Function requires 1 arguments.

Some advise please.
David

 

[donutman]

This is one of those jobs that you shouldn't bother trying to do in one step. Peal off the last 5 digits and put them in the zip field and put the remaining string in another field (temporary). Look at the results (sort on zip). Adjust and rerun if necessary because of potential data problems, eg. 9 digit zip code or no zip code, etc. You will probably have to make manual corrections too. Then try to peal off the state code...
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Idokyoku2]

I unfortunately have a number of these tables, but I'll give
it a go!
Thanks,
David