Split Data within a column.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: AndrewT. (AndrewT_at_discussions.microsoft.com)
Date: 03/09/05 

Date: Wed, 9 Mar 2005 08:31:05 -0800

I have a database with 146,000 records. I have a field named Zip_10 (text).
It
contains zipcodes with and without zip+4. the data is entered as follows:

48006
48123-0021
48323-0989
48123
48945-2321
All the records with a zip4 have a hypen. I need to make one column zip
(first 5 digits) and one column zip4 with the digits past the hypen.
I have created a new column called Zip4 but it is empty. I am below NOVICE
level on this stuff. I can do it in excel by using the text to column
command but my full database will not load. I truncates at 65,000 records or
so.

UPDATE MyTable
    SET Zip_4 = Right$(Zip_10, Len(Zip_10) - (Instr(Zip_10, "- "),
  Zip5 = Left$(Zip_10, Instr(Zip_10, "- ")

Any help? The above doesn't work and I got It off this site. (I change the
names to fit my data).

Relevant Pages

  • Bar codes in an IF/THEN statement in Mail Merge
    ... I have an ODBC connection to a database on another ... IF the ZIP4 field is blank, ... Excel and doing the IF/THEN there, ...
    (microsoft.public.word.mailmerge.fields)
  • Zip code
    ... I have a database of zipcodes with latitude and longitude. ... method of calculating the distance between two zipcodes. ...
    (comp.lang.php)
  • Re: Obtain distances using zip codes?
    ... I don't know if this newsgroup will let me attach, but here is a database ... with all of the zipcodes and the distances from 01923. ... determine driving distance between my office address versus each employee ...
    (microsoft.public.access.macros)
  • US Zipcode API for Ruby?
    ... Does anyone know of API (and database) for Ruby to provide information ... Also helpful would be zipcodes within a given ... If something doesn't exist natively for Ruby I might utilize a web service. ...
    (comp.lang.ruby)
  • Re: zip code from latitude-longitude?
    ... the Drupal project has a location module which contains a database table for zipcodes as well as data for US and Germany... ...
    (sci.geo.satellite-nav)