Hi,
I have to replace a table record with correct format of data. I have the data (zip ) in various invalid formats that I am interested to query the records where zip is 5 digits followed by '-' (hyphen), OR 5 digits followed by '-' (hyphen) then followed by atleast 3 digits max. thank you.
Invalid formats are: (need to query only below format rows and update these below records with first 5 digits, trim the extra char's from '-' till 3 digits after '-')
75028- replace with 75028
75028-1 replace with 75028
75028-12 replace with 75028
75028-123 replace with 75028
Correct formats:
75028
75028-1234
create table valid (ID varchar2(4), zip varchar2(12));
insert into valid (ID, zip) values ( '1111','75028-');
insert into valid (ID, zip) values ( '2222','75028-1');
insert into valid (ID, zip) values ( '3333','75028-12');
insert into valid (ID, zip) values ( '4444','75028-123');
insert into valid (ID, zip) values ( '5555','75028');
insert into valid (ID, zip) values ( '6666','75028-1234');
insert into valid (ID, zip) values ( '1234','75028.1234');
insert into valid (ID, zip) values ( '1324','750282348');