Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to convert int to char with leading zerros?

Posted on 2004-08-26
19
Medium Priority
?
721 Views
Last Modified: 2012-06-21
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
Comment
Question by:patrikt
  • 6
  • 5
  • 3
  • +2
18 Comments
 
LVL 3

Expert Comment

by:alexpreston
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

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

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

Expert Comment

by:alexpreston
ID: 11900663
declare @x char(4)   (OOPS!)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Accepted Solution

by:
alexpreston earned 150 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

by:alexpreston
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

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

Expert Comment

by:Otana
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

by:SQL_Stu
SQL_Stu earned 75 total points
ID: 11900842
How about just straight:

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

by:ill
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)

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

Expert Comment

by:Otana
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

by:SQL_Stu
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

by:Otana
ID: 11900969
btw, my last comment was meant for SQL_Stu.
0
 
LVL 11

Expert Comment

by:Otana
ID: 11900973
But you're right, my mistake. Sorry.
0
 
LVL 12

Author Comment

by:patrikt
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

by:ill
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

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

Author Comment

by:patrikt
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

by:alexpreston
ID: 11902272
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question