format number to string - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

format number to string

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2009-12-24 : 13:51:59

Hi,

in my database i am storing ponumber as integer example 1
but i need to show in the front end query ponumber 0001 how to use format function

Desikankannan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-24 : 14:08:39

Do this formatting in your application and not in SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-25 : 13:41:04

Tara is right.
But if this is not possible for you then do it like this:
select
right('0000'+convert(varchar(20),ponumber),4) as ponumber
from your_table

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-28 : 00:56:06

or

select
replace(str(ponumber,4),' ','0') as ponumber
from your_table

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2009-12-28 : 01:53:04

declare @ponnum as int
select @ponnum=1
select right('000' + convert(varchar(4),@ponnum),4)
select @ponnum=11
select right('000' + convert(varchar(4),@ponnum),4)
select @ponnum=111
select right('000' + convert(varchar(4),@ponnum),4)
select @ponnum=1111
select right('000' + convert(varchar(4),@ponnum),4)

PBUH

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-12-28 : 02:13:45

SELECT RIGHT(REPLICATE('0',4)+CAST(1 AS NVARCHAR(1)),4)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-28 : 04:11:45

quote:
Originally posted by ms65g

SELECT RIGHT(REPLICATE('0',4)+CAST(1 AS NVARCHAR(1)),4)

Why are you converting 1 to NVARCHAR when VARCHAR is just enough?

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-12-28 : 09:27:24

quote:
Originally posted by madhivanan

quote:
Originally posted by ms65g

SELECT RIGHT(REPLICATE('0',4)+CAST(1 AS NVARCHAR(1)),4)

Why are you converting 1 to NVARCHAR when VARCHAR is just enough?

Madhivanan

Failing to plan is Planning to fail

Good mention.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources