PL/SQL (MOSC)

MOSC Banner

repair bad data from table

edited Sep 7, 2013 6:13AM in PL/SQL (MOSC) 3 commentsAnswered
 Hi,


create table badtable (data varchar2(30), data_mig varchar2(30));

insert into badtable(data) values ('12345678.25368'); 
insert into badtable(data) values ('<12345678.25368');
insert into badtable(data) values ('12345678.25368'); 
insert into badtable(data) values ('12345678>.25368');
insert into badtable(data) values ('12345A678.25368');
insert into badtable(data) values ('12345A678D.25368');
insert into badtable(data) values ('D12345678.25368');
insert into badtable(data) values ('12345678A.25368');

select * from badtable;

DATA                           DATA_MIG                     
------------------------------ ------------------------------
12345678.25368                                                
<12345678.25368                                               
12345678.25368                                                
12345678>.25368                                               
12345A678.25368                                               
12345A678D.25368                                              
D12345678.25368                                               
12345678A.25368                                               

 8 rows selected 


Now.. I want to update DATA_MIG column... to update only numeric character not <, >, D, A character. expected result as below.. after updated.  means update only numeric character to DATA_MIG column only. the below is only example... it is fix value, value can be anything.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center