Hi People,


I don't do the DDL's , am just a programmer. But i have following
requirements.
I have to expand a CHAR(9) field to CHAR(12) in a table. Also , i
have to place 3 leading zeros to the
expanded bytes. I mean if i was having '123456789' in the old
field , now i have to have '000123456789' in the
modified field. How can i do that?

Thanks in advance,
Shishir

On 2010-09-20 23:36, Shishir wrote:
The vanilla way (9.5 LUW):


select * from T2;
X
---------
123456789

alter table T alter column x set data type char(12);
reorg table T;
update T set x = '000' || rtrim(x);

select * from T2;
X
------------
000123456789

/Lennart

On 2010-09-21 00:24, Lennart Jonsson wrote:
[...]

make that:


> /Lennart

On Sep 20, 3:53*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Thanks Lennart
I have another condition added here.
Suppose i don't know how many leading zero's i need to add - it may be
3 , 4 or 5 , how can i go about that.

On 2010-09-21 01:23, Shishir wrote:
[...]
Not tested so there might be an off by one error or such, but the idea
should be ok:

update T2 set x = repeat('0', 12 - length(rtrim(x))) || rtrim(x)


/L