Comparing two fields - will soundex work?

[rosn459]

I've got two fields in two different tables that contain city and state information. TblOne has city/state format. TblTwo has state/city format. Will soundex work to compare or match the two tables to see if the same city/state and state/city combination exists?

It seems like I did this before; but I can't remember exactly how I did it...

 

[JamesLean]

If you mean you may have two values like "Sacramento CA" and "CA Sacramento" then no, SOUNDEX won't see those as the same.

If you can give us some examples of the different data that you want to match then someone may be able to suggest a solution.

--James

 

[SQLSister]

By far the best solution is to fix your poor database design. State and city should not be stored in the same field. Parse the data out into a city column and a state column, then you queries will be simple.

If you must leave the other structure too to avoid breaking other things, then add the columns and add triggers to update the new columns on insert and update.

 

[rosn459]

SQLSister,

Parsing the data - that's what I probably did!!! Thanks for the tip and for triggering the reminder!!!