At 09:18 AM 11/4/2009, Dan Goldberg wrote:
I have data that I receive from a vendor for canadian zipcodes and need to
put a space after the first three characters.
V0R1R1 Need to be V0R 1R1
Dan,
In your specific scenario, if the same column contains both U.S. Zip Codes
(Begin with Digits) and Canadian Zip Codes (Begins with Alpha), you may try
the following:
-- Example:
UPDATE tablename SET ZipCode = +
(IFEQ((ISALPHA(SGET(ZipCode,1,1))),1,((SGET(ZipCode,3,1))&(SGET(ZipCode,3,4))),ZipCode))
RETURN
This will ONLY update the Canadian Zip Codes that start with alpha
character, as desired.
Of course, you will only have to use this SELECTIVE UPDATE only once
after loading the
new data into a temporary table to fix the Zip Codes and then use the
Append or Insert
command accordingly to populate the actual table.
Hope that helps!
Very Best R:egards,
Razzak.
