Split up data

[kitty77]

I have a table that has the city, state and zip combined in one field.

Looks like this: Columbus, OH 43217

How can I split up the data into three separate fields in a query?

Thanks!

 

[MadPiet]

I always do this in PowerQuery now... but I'd do it like this:
1. split the string on the comma. The "left" side is City. The right side is State and Zip Code
2. Trim the State and ZipCode to remove the leading and trailing spaces.
3. Split the result on [Space] or double-space. then you get State (left) and Zip Code (right).

 

[Cotswold]

another way is using string handling

mVar1 = Len(Trim(Field))
mVar2 = instr(field,",")
mVar3 = instr((mVar2+2),Field,Space(1))

mNew1 = left(field,(mVar2-1))
mNew2 = mid(field, mVar2+2, 2)
mNew3 = Right(trim(field),mVar1-mVar3+1)

 

[xavier.batlle]

In a perfect world, if the data always follows the same pattern, this could be a way to achieve it.

SELECT Customers.Address, Left([Address],InStr([Address],",")-1) AS City, Mid([Address],InStr([Address],",")+2,2) AS State, Mid([Address],InStrRev([Address]," ")+1) AS Zip
FROM Customers;

 

[Ken Sheridan]

I have a table that has the city, state and zip combined in one field.

Looks like this: Columbus, OH 43217

Combining three separate attributes in a single column is not good relational database design. A principle of the database relational model is that each column position in each row in a table should be atomic, i.e. it should contain only one legitimate value, of the attribute type represented by the column.

Amending the design is simple provided that the value in each column is consistently formatted and enables it to be parsed by one of the methods suggested by my colleagues. Firstly change the column's name to City, and add two new columns, State and Zip. Then execute an update query which updates the values of the State and Zip columns, using one of the expressions you've been given. Confirm that the two new columns have been updated successfully, and if so execute another update query which updates the City value, again using one of the expressions which you've been given.

Your table design is now better, but this is not the end of the story. By including the State column in the same table as the City column the table is not normalized to Third Normal Form (3NF) as State is functionally determined by City. 3NF requires that all non-key columns are functionally determined solely by the whole of the table's primary key, which City might not be of course. Your table would thus be open to the risk of update anomalies.

Another problem is that city names can legitimately be duplicated, and there are numerous examples of this both in the USA and the UK. The table, therefore, should contain a CityID column of long integer number data type, referencing the numeric primary key, usually an autonumber, of a separate Cities table, which will also contain a City column with the city names. Two or more cities of the same name can then be included, with separate distinct CityID values.

However, the plot thickens even further as in the USA a single city can be in more than one state, Kansas City being an example, so there is a many-to-many relationship type between cities and states. This is modelled in the usual way by a table which resolves the relationship type into two one-to-many relationship types. This is illustrated in the image below of the model for the attached demo file:

In the above Regions is analogous to States in the USA. I've used the generic term because the demo uses international data, so different countries have different regional structures. Here in the UK County is the regional unit for instance.

Post codes such as Zips codes are simpler to handle, as these can be related to Cities, as shown above. A city will usually include multiple post codes, and the same post code might span city boundaries.

Decomposing your single table would require the execution of a set of 'update' and 'append' queries in a specific order. Without knowing the exact structure of your table I can't give you precise details of this, but if you'd care to post an empty copy of the table here, I'd be happy to let you have a file with the necessary queries to decompose it into a model like that illustrated above.

 

[RonPaii]

Sometimes I think switching to a property based system would be cleaner; allowing searching for things like a City in a state and multiple counties.

tblPropType
ID
Desc

1, City
2, StateAbv
3, PostalCode
4, StreetAddress
5, StreetName
6, County
7, Country

tblAddress
ID
ContactdID
PropTypeID
PropVal

1, 234, 1, Columbus
2, 234, 2, OH
3, 234, 3, 43217
4, 234, 4, 234
5, 234, 5, Park Way Av
6, 234, 6, Franklin
7, 234, 7, USA

Where City = "Columbus" and State = "OH" and (County = "Franklin" or County = "County" or County = "Fairfield")