format numeric 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

General SQL Server Forums

New to SQL Server Programming

format numeric to string

Author

Topic

dainova
Starting Member

31 Posts

Posted - 2009-07-23 : 19:25:28

Hi,
Anybody can suggest compact way to to the following converson:
IF Price=0 then '001000' i.e.=1(one) in 6,3 num format, no decimal point needed
IF Price=null then '001000'
IF Price=numeric then this numberin in 6,3 format, no decima,e.g. 7-->'007000', 15.3--> '015300',etc.

I'm using this in select, column Price is (Numeric(9,4).
when im doing this I still get wrong result <001>:
case when Price=0 then -- should be 1(one)
REPLICATE('0', 3-(LEN(LTRIM(substring(str(1,9,4),2,3)))))
+(LTRIM(substring(str(1,9,4),2,3)))
Thanks
Dai

singularity
Posting Yak Master

153 Posts

Posted - 2009-07-23 : 20:05:53

[code]
case when price = 0 or price is null then '001000'
else right('000000' + (cast((price * 1000) as varchar(6)), 6) end
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 04:20:59

If you want show data in front end application, do formation there

Madhivanan

Failing to plan is Planning to fail

dainova
Starting Member

31 Posts

Posted - 2009-07-24 : 13:40:59

Thanks, singularity!

"else" portion Its doesn't work form me, giving:
Server: Msg 8115, Level 16, State 5, Line 1
Arithmetic overflow error converting numeric to data type varchar.
else right('000000' + (cast( (price * 1000) as varchar(6))), 6) end
* i added 1 extra ) in (6)))

Price is (Numeric(9,4). It started working when varchar(n) go n> 9 ,but with wrong result.

Dai

singularity
Posting Yak Master

153 Posts

Posted - 2009-07-24 : 19:54:06

Can you provide some sample data?

dainova
Starting Member

31 Posts

Posted - 2009-07-28 : 17:29:50

Tx, singularity

As I mentioned with varchar(6) I get overflow errror, It works with:
else right('000000' + (cast( (strikemultiplier * 1000)

---------------- --------- ---------
.0000 computed= 001000
10.0000 computed= 0000.0000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-28 : 17:52:20

Keep it simple...

DECLARE	@Sample TABLE
	(
		Price DECIMAL(38, 12)
	)

INSERT	@Sample
SELECT	0 UNION ALL
SELECT	NULL UNION ALL
SELECT	15.3

SELECT	Price,
	REPLACE(STR(1000 * COALESCE(NULLIF(Price, 0), 1), 6, 0), ' ', '0')
FROM	@Sample

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources