Solved

How to convert int to char with leading zerros?

Posted on 2004-08-26
19
714 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

726 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