Solved

# How to convert int to char with leading zerros?

Posted on 2004-08-26
715 Views
I have to convert int numbers to char(4). Problem is that I have to have leading zeros there.

1 be '0001'
12 be '0012'

atc.

How to do that with simple expression?
0
Question by:patrikt
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 5
• 3
• +2

LVL 3

Expert Comment

ID: 11900649
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')
0

LVL 11

Expert Comment

ID: 11900659
declare @int int
declare @string char(4)

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

LVL 3

Expert Comment

ID: 11900663
declare @x char(4)   (OOPS!)
0

LVL 3

Accepted Solution

alexpreston earned 50 total points
ID: 11900690
Actually my method doesn't seem to work. But then again neither does Otana's.

But a slight variation on Otana's does :

declare @int int
declare @string char(4)

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

LVL 3

Expert Comment

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

LVL 12

Assisted Solution

ill earned 50 total points
ID: 11900835
select  right('0000' + cast( 1 as varchar(40)),4)
0

LVL 11

Expert Comment

ID: 11900836
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'
0

LVL 7

Assisted Solution

SQL_Stu earned 25 total points
ID: 11900842

Select CASE Len(@int) WHEN 1 Then '000' + cast(@int as varchar)
when 2 Then '00' + cast(@int as varchar)
when 3 Then '0' + cast(@int as varchar)
when 4 then cast(@int as varchar) END

0

LVL 12

Expert Comment

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

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

LVL 11

Expert Comment

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

LVL 7

Expert Comment

ID: 11900959
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".
0

LVL 11

Expert Comment

ID: 11900969
btw, my last comment was meant for SQL_Stu.
0

LVL 11

Expert Comment

ID: 11900973
But you're right, my mistake. Sorry.
0

LVL 12

Author Comment

ID: 11901413
Endo of the test guys....I solved that.

I'll chose this:

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

Points will be split.
0

LVL 12

Expert Comment

ID: 11901488
I'm terribly sorry for not guessing your expectations correctly  and would prefer my comment not beeing accepted as assisted answer than getting "C".
http://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)
0

LVL 3

Expert Comment

ID: 11901704
I agree. Why the 'C'? Surely we weren't that bad ??
0

LVL 12

Author Comment

ID: 11902263
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
0

LVL 3

Expert Comment

ID: 11902272
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve thâ€¦
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages â€“ plus untold reputational damage to one of the worldâ€™s most trusted airlines. All due to a catastrophâ€¦
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month5 days, 9 hours left to enroll