format of number - 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 of number

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-11-19 : 04:13:58

if i have number like 700 and i want to output to format of

0000700.00

another example

i have a number 4750.455 and the ouput need to be 0004750.45

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 04:23:54

just use

RIGHT('0000'+ CAST(FLOOR(Numberfield) as varchar(4)),7)+ SUBSTRING(NumberField,CHARINDEX('.',NumberField),LEN(NumberField))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 04:37:22

SELECT REPLACE(STR(@i, 10, 2), ' ', '0')

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 04:47:31

[code]DECLARE @Sample TABLE
(
i DECIMAL(9, 2)
)

INSERT @Sample
SELECT 700 UNION ALL
SELECT 4750.455

SELECT i,
REPLACE(STR(i, 10, 2), ' ', '0')
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-11-19 : 07:48:04

thanks to all

i like what Peso do,it is work.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-20 : 02:37:53

Dont fully rely on STR

SELECT STR('12345678901')

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-20 : 02:44:05

Just as declaring VARCHAR without a determined number of characters, STR defaults to 10 characters insluding comma and decimal digits.

SELECT STR('12345678901', 11) -- will work

declare @r varchar
set @r = 'madhivanan'
select @r -- Just shows the initial m character.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-20 : 04:04:51

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources