Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

adding leading zeros to number value

elmasduroJul 11 2019 — edited Jul 11 2019

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

This post has been answered by mathguy on Jul 11 2019
Jump to Answer
Comments
Post Details
Added on Jul 11 2019
2 comments
26,620 views