Solved

How to convert int to char with leading zerros?

Posted on 2004-08-26
19
698 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
 
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now