Update/insert substr in middle of a column

[raygg]

I am fairly new to Oracle but do not know how to handle this update problem. I have a column date_time Varchar2 (14) containing a date yyyymmddhhmmssxx, where xx = hundredths of a second. Some data is faulty and contains blanks in 1, 2, or 3 positions (the low order mm and all of ss). It was loaded from a character file where the non-Oracle creating program did a high order truncation of the hh mm ss fields so a time like 07:03:01 winds up as 731bbb, where b=blank character. The approved solution is to plug 01010101 in the hhmmssxx positions whenever a blank character appears in position 14. The one other issue is that the entire field can be null - those records are to be ignored. I am not sure how to do this - help!

200012229295 64
null
2000122214647 20
2000122210182785
null
2000122214716 62
200011271284 65
2000122210183042

 

[carp]

Try this:
UPDATE my_table
SET date_time = substr(date_time,1,8)||' 01010101'
WHERE date_time IS NOT NULL
AND substr(date_time,14,1)=' ';

 

[raygg]

Thanks so much - that works!