I have a string, for example X1. I need to create a new string by inserting zeros into it. To be more specific:
for X1 the new string will be X0001; for X10 the new string will be X0010; for X100 the new string will be X0100; for X1000 the new string will be X1000. The length of the new string is 5.
I have tried to do this:
select substr('X1', 1, 1) || '000' || substr('X1', 2) from dual;
But this solves only the first case and in returning X0001.
Try using LPAD
with SUBSTR
:
SELECT col, SUBSTR(col, 1, 1) || LPAD(SUBSTR(col, 2), 4, '0') AS col_padded FROM yourTable;