Remove square bracket from DB2 string and add leading zeros

Question

I'm using DB2 11. I have a table employee & it contains one column as employee_id. It has 114131 records. Out of these records around 20% cases employee_id contains record in this format [12345678] & remaining 80% cases it's in this format 0012345678.So for these 20% cases, I want to remove the square brackets from emp_id and add 2 leading zeros to make it 10 characters long in employee_id column if square bracket is there. Can you please suggest me how to do that?

Answer 1

One way to do this with Db2 v11 on Windows (other ways are possible) is: lpad(regexp_extract(employee_id, '\d+'),10,'0') so you would use that in a SELECT or UPDATE as appropriate. This would remove any non digit characters, assuming that was the real intent, not only the [ ] .