How to convert a number to a fixed length string? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to convert a number to a fixed length string?

Author

Topic

zhangn
Starting Member

29 Posts

Posted - 2009-10-15 : 11:57:42

Hi all

Is there any way I can convert a number to string in sql 2005?

for example

1 -> 01
2 -> 02
10 -> 10
11 -> 11

many thanks!

Ning

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-15 : 12:01:04

right('00' + convert(varchar(2), num), 2)

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-15 : 12:01:59

Yes!
concatenate it!
declare @testvalue int
set @testvalue=1
select right('00'+convert(varchar(2),@testvalue),2)

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-15 : 12:02:48

I am old and slow...

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-15 : 12:03:35

only 55 secs

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-15 : 12:04:39

That makes me sleepless

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-15 : 12:05:47

while most of you are asleep . . i am awake

KH
[spoiler]Time is always against us[/spoiler]

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-15 : 13:05:45

quote:
Originally posted by khtan

while most of you are asleep . . i am awake

KH
[spoiler]Time is always against us[/spoiler]

But you never sleep....hence the title

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-16 : 03:45:40

quote:
Originally posted by zhangn

Hi all

Is there any way I can convert a number to string in sql 2005?

for example

1 -> 01
2 -> 02
10 -> 10
11 -> 11

many thanks!

Ning

Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-16 : 03:47:37

If it is only for display

select replace(str(col,2),' ','0') from your_table

provided all numbers are between 0 and 99

Madhivanan

Failing to plan is Planning to fail

zhangn
Starting Member

29 Posts

Posted - 2009-10-16 : 04:52:57

Thanks for all your help. it works. The reason i need that is because i am trying to pivot data.
Row will be

Day1-01,Day1-02.....Day1-03

If i don't get 01, 02....
The rows will not be in an order.

Thanks. I think the probelm can be solve now.

Very appreciated for all your help.

Ning

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources