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
 formatting a select of a numeric column as format -9999.9999

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-25 : 10:38:23
scott writes "I am relatively new to SQL Server, but after a couple of hours searching help, I'm stuck.

I am trying to select a numeric column from a table and format it using sql. Desired format "-9999.9999" with leading sign and zeros. IS_NEGATIVE is a function to determine sign. The code below works, but seems inefficient.

Is there a better way?

SELECT
RIGHT(dbo.IS_NEGATIVE(ISNULL(COST,0)),1) + RIGHT(REPLICATE('0',10) + RIGHT(abs(CONVERT(decimal(9,5),COST)),10),10) AS 'PRICE'
FROM ORDERS"

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-25 : 11:21:43
Give some sample data and expected results

Srinika
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-25 : 11:24:56
Better place to do this kind of formatting stuff is in the front-end...SQL may not be the best candidate for it !

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-25 : 23:44:18
Where do you want to show data?
As suggested if you use front end application, use format function there

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-26 : 10:48:16
SELECT CASE WHEN Cost > 0 THEN '+' ELSE '' END + CONVERT(VARCHAR, CAST(Cost AS MONEY), 2)

Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -