Dear SQL Experts,
I want to write SQL to format a column value as follows.
(If current value is B-5-4, I want it to be B-05-0004, if it's B-5-86, I want it B-05-0086, and if it's B-5-876, I want it B-05-0876).
That is basically, append 3, 2 or 1 zeros after 2nd hypen and append exactly 1 zero after first hyphen.
Can this be done using regex_replace ?
Someone helped me with below code recently, but it will only append 0's after second hyphen. How can I modify this to append 0's after first hypen as well.
update owner.test1219
set TNPR_STRC_ID = substr(TNPR_STRC_ID, 1, instr(TNPR_STRC_ID, '-', 1, 2)) || lpad(substr(TNPR_STRC_ID, instr(TNPR_STRC_ID, '-', -1) + 1), 4, '0')
where regexp_like(TNPR_STRC_ID, '^[A-Z]\-\d{1}\-\d{1,3}$', 'i')
Appreciate all help !!!