Hi everyone,
i have a scenario where i have a table with a column which is varchar2. the data i get from source is not always perfect.
consider the following data
with data as
(
select 1 cid from dual union all
select 2 cid from dual union all
select 5 cid from dual union all
select 33 cid from dual union all
select 10 cid from dual union all
select 11 cid from dual union all
select 133 cid from dual union all
select 200 cid from dual union all
select 500 cid from dual union all
select 600 cid from dual
)
the data i get comes as 3 digits cid but sometimes some of the values dont come as 3 digits as illustrated in the data above.
in case that case, i want to add leading zero to those values that are not 3 digits so that i can make it as 3 digits values
for example, 1,2,33 are not 3 digits values so i should add leading zero such as 001, 002, 033. now they are 3 digits values
200,500 are already 3 digits so no need to add leading zeros.
my output should be
cid
===
001
002
005
033
010
011
133
200
500
600
i was thinking regular expression would do the trick but im not too familiar how to use them.
can someone help write a query that adds leading zero to those values that are not 3 digits in length?
im using oracle 11g
thanks in advance