| Fixing data [message #2841] |
Fri, 16 August 2002 06:30  |
Tam Beck
Messages: 1
Registered: August 2002
|
Junior Member |
|
|
Hi,
I am looking to fix data. I have a test table that has a zip code field. It is defined as a Number 9 with no constraints. I have been asked to find a way to fix this data if the user only enters in a 5 digit zip. They want me to append 4 zeros to the zip if it is < 9. How should I go about this? Will I need a trigger to fix incoming data and a procedure to fix the data already in this field?
Thank you!
Tam
|
|
|
|
| Re: Fixing data [message #2848 is a reply to message #2841] |
Fri, 16 August 2002 09:27  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
If you don't want to have to keep fixing the data, then yes, you will need a one-time cleanup and a trigger.
The cleanup would be:
update t
set zip_code = rpad(zip_code, 9, 0)
where length(zip_code) < 9;
The trigger could be:
create or replace trigger t_trg
before insert or update on t
for each row
begin
:new.zip_code := rpad(:new.zip_code, 9, 0);
end;
/ |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
