Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need with regex_substr & regex_replace

OraAskNov 15 2018 — edited Nov 16 2018

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');

Comments
Post Details
Added on Nov 15 2018
16 comments
696 views