How can I pad '0' to a fixed length char?

[jliang]

Hi,

I need pad the '000' to the char field, such as col char(10), and the data only has 5 digits, I need pad 5 '0' at begin of the string, like '0000012345'. Can you help me with it?

Thanks

Jing

 

[dnoeth]

Is the original column numeric?
select col (format '9(10)')

Is the original column char?
left aligned ('12345'):
select '00000' || col

right aligned or varchar (' 123'):
select
substring('0000000000' from char_length(trim(col)) + 1) || col

Dieter

 

[jliang]

Dieter,

It is work fine for select statement. I try to use this format in the fastload with insert into statment, it does not take it. do you have any idea if I can do it in the insert into table value('00'||:col)?

Many Thanks

Jing