Format Integer to Have Preceeding 0's

[AccessUser22]

If I have an integer being passed in, say 2 for example, and I want to store this in part of a string as 002, is there a way to set this up during my stored procedure using the CAST function? Any ideas are appreciated.

 

[ptheriault]

Did you what something like this?

declare @num int

select @num = 1

select '00'+convert(varchar(100),@num)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mflancour]

convert(varchar(3), REPLICATE('0', 3 - LEN(@num))) + convert(varchar(3), @num)

 

[monksnake]

SELECT RIGHT('000' + CONVERT(CHAR(3), @num), 3)

<.

 

[ptheriault]

I guess the question should be.. Do you always want to pad all your ints with leading 'O's. like
001
0010
00100

Or are you looking for something more like this.

001
010
100




- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[bborissov]

monksnake, if you want right results you must CAST to varchar not to CHAR()

SELECT RIGHT('000' + CONVERT(varchar(3), @num), 3)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[monksnake]

AAAAHHHh ok, thanks for the info bborissov

<.

 

[AccessUser22]

I'm looking for something more like

001
010
100

I'm gonna give the above example

SELECT RIGHT('000' + CONVERT(varchar(3), @num), 3)

a try. Thanks!