Solved

How to convert int to char with leading zerros?

Posted on 2004-08-26
19
715 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
[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
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

707 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