Solved

How to convert int to char with leading zerros?

Posted on 2004-08-26
19
713 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
19 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 3

Accepted Solution

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

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 50 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 25 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS - Date Report Options 2 28
Download ms sql express. 2 25
Update one table with results from another table in SQL 6 22
What is this datetime? 1 18
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

839 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