How to convert int to char with leading zerros?

CONVERT(char(4),1) will return '   1'

So then you can use REPLACE.

declare x char(4)
set x = REPLACE(CONVERT(char(4),[your number]),' ','0')

declare @int int
declare @string char(4)

set @string = right('0000' + cast(@int as char(4)),4)

declare @x char(4)   (OOPS!)

It's because CASTing or CONVERTing   1 to char(4) returns '1   ', and not '   1' as I stupidly stated to start with!

alex is right, my first solution doesn't work. However, this one does:

declare @int int
declare @string char(4)

set @int = 1

set @string = right('0000' + ltrim(rtrim(cast(@int as char(4)))),4)

select @string

This will return '0001'

@otana let  conversion trim whitespaces ;)
right('0000' + ltrim(rtrim(cast(@int as char(4)))),4) = right('0000' + cast( @int as varchar(4)),4)

-- this is common way of adding leading zeros
right('0000' + cast( @int as varchar(4)),4)

That won't work because you're casting your int to a varchar of size 1 each time.

Nope it isn't.

It will convert the int as necessary.  ie. 3 will be converted to "3", 34 will be "34", 345 will be "345".

btw, my last comment was meant for SQL_Stu.

But you're right, my mistake. Sorry.

Endo of the test guys....I solved that.

I'll chose this:

right(cast(10000+@x as varchar),4)


Points will be split.

I'm terribly sorry for not guessing your expectations correctly  and would prefer my comment not beeing accepted as assisted answer than getting "C".
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi73

right('0000' + cast( @int as varchar(4)),4)
right(cast(10000+@x as varchar),4)

I agree. Why the 'C'? Surely we weren't that bad ??

Oh.. Sorry for that C. It was my mistake.
I wanted to give B as it is was good standard answer.

I'm working on two projects together and as third job reading EE. Sorry once more.

Is there any way how to change it?

Patrik

Don't worry.

https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi18