Separating Address Field into Multiple Fields

[yldchld13]

I'm new to both SQL and this message board so please be kind.

I've got a large dataset (1.9 million records) with various information. Within this dataset, there is an address field that is specific to each record. Unfortunately, to do what I need to do with this information, the address information needs to be split into other fields (name, street address, city, state). Records differ from each other in that one may have a name and the next may not. Each normally has multiple spaces where I would need to split them, but again, that differs from record to record.

Since I'm new to SQL, I'm either not grasping the entire process or I'm completely over thinking it... but nothing is working. Any assistance would be greatly appreciated! Thanks in advance.

[sandyboy]

I'm new to both SQL and this message board so please be kind.

I've got a large dataset (1.9 million records) with various information. Within this dataset, there is an address field that is specific to each record. Unfortunately, to do what I need to do with this information, the address information needs to be split into other fields (name, street address, city, state). Records differ from each other in that one may have a name and the next may not. Each normally has multiple spaces where I would need to split them, but again, that differs from record to record.

Since I'm new to SQL, I'm either not grasping the entire process or I'm completely over thinking it... but nothing is working. Any assistance would be greatly appreciated! Thanks in advance.

You can try this using string functions
Create a table called table1 having columns address information,city,state,zip,name.
Now insert the address information from original table to table1.Now you have to split this address information into city,state,zip,name.

code to extract the city name only

UPDATE table1
SET CITY=Rtrim(substring(city,1,charindex(',',city) -1))

code to extract zip only

UPDATE APR07
SET ZIP=LTRIM(SUBSTRING(city, CHARINDEX(' ', city) + 1, LEN(city)))