Numeric Column

[banker_kiran]

Hi, I have one column with numeric data type. It is auto number. i have given size numeric(9,0) now i want to pad 0. eg. if it is 1 in auto number then i want to display 000000001 if 2 then 000000002 if 10 then 000000010

kiran banker

[Krish KP]

check is this what you are looking for SELECT CASE WHEN Len('00000000' + LTrim(str(myid))) > 9 THEN SubString( '00000000' + LTrim(Str(myid)), Len('00000000' + LTrim(Str(myid))) - 9, Len('00000000' + LTrim(Str(myid))) ) END FROM tableA

Regards KP

[GuyThiebaut]

Hi Kiran, The code below will insert '15' into ten zeroes and keep it right justified. You will just need to jiggle things a bit substituting your number for '15' select stuff('0000000000',11-len('15'),len('15'),'15') Or even better select stuff(replicate('0',10),11-len(cast(15 as varchar(100))),len(cast(15 as varchar(100))),cast(15 as varchar(100))) I hope this is of help. Guy

You always pass failure on the way to success.

[ianhoc]

I think this is easier: SELECT RIGHT('00000000' + Ltrim(str(MyKey)), 8) From Table Ian