Instead of trying to do this using SQL you might try using MS Excel, or a program like TextPad that supports regular expressions. I would personally lean towards trying to use regular expressions to split apart the text.
If you have less than 100 records it is probably faster to do the parsing manually. Maybe ask your boss to purge irrelevant contacts before you start to clean them up. -Mike Chabot On Mon, Feb 8, 2010 at 11:34 AM, Douglas Brown <[email protected]> wrote: > > My boss has a contacts database that is very out of date and not really that > useful for his purpose. The database program he is using can export the data > into a tab delimited file for import into the new database that I am > designing. My problem is the way the old data is formatted. I would like to > have city, state and zip separated into separate fields but the old data in > the text file is formatted with the city, state, zip in one field. How can I > pull the data out of the old field and populate the new database with it > without following the same scheme. I have also noticed that there is no > standard delimiter that was used to separate those values. IE: city, state, > zip and city state zip. Hoping this makes sense. > > > Thanks > > > Doug > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3271 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
