Formatting issue with a calculated value

Question

Formatting issue with a calculated value

arratialino

Grasshopper

Points: 13
More actions
August 8, 2009 at 7:04 pm

#97970

Hello fellows,
I am having a bit of a jam here..
I am working on a 2005 database. And I am using this piece of code to display a result value with three decimals.
ROUND((100/ABS(VALUE)), 3)
The values being returned sometimes bring 0, 1, 2 or 3 decimals. I need them all to display the 3 decimals, adding 1, 2 or 3 zeros depending on the end result.
Can somebody help me with it?? I have tried to convert it to text, then search for the dot "." and add the missing zeros, but the end value might come as a whole number without the dot. Help please.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You can do it with a comibnation of string functions such as charindex and reverse, but the question is why do you want to do it in the database? This kind of things should be done in the presentation layer and not in the data layer. The presentation layer has better ways of doing it. Having said that , the code bellow does it with SQL, but I strongly advice that you won’t use this code and that you’ll do it in the presentation layer instead

declare @value float

set @value = 40

select convert(varchar(8),ROUND((100/ABS(@VALUE)),3)) +

case when charindex('.',reverse(ROUND((100/ABS(@VALUE)),3))) = 0 then '.000'

when charindex('.',reverse(ROUND((100/ABS(@VALUE)),3))) < 4 THEN replicate('0',4-charindex('.',reverse(ROUND((100/ABS(@VALUE)),3))))

else ''

end

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul White SSC Guru Points: 150468 More actions · Answer 2

declare @value int;

set @value = 7;

select CONVERT(DECIMAL(9,3), ROUND((CONVERT(DECIMAL(38,10), 100)/ABS(@value)), 3));

-- OR

select CONVERT(DECIMAL(9,3), ROUND(100.0/ABS(@value)), 3));

The key is to watch the data types. I have guessed at @value being an INTEGER and the precision and scale of the DECIMALs used is kinda arbitrary, but hopefully you will see the idea. If there is a 'presentation layer' outside SQL Server, I would tend to agree that formatting should be done there.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

No need for rounding if you're going to cast the answer...

SELECT CAST(100.0/ABS(VALUE) AS DECIMAL(9,3))

For example...

SELECT CAST(100.0/ABS(6) AS DECIMAL(9,3))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 4

Jeff Moden (8/9/2009)
No need for rounding if you're going to cast the answer...

Hey Jeff,

Valid point, but let me explain myself: I left the ROUND in for two reasons:

1. To make keep it as close to the original code as possible to make my change clearer

2. To avoid possible subtle changes in behaviour (see not just the values, but the types below)

Consider:

SELECT CONVERT(DECIMAL(9,3), 2.0005); -- 2.001 DECIMAL(9,3)

SELECT ROUND(2.0005, 3); -- 2.0010 NUMERIC(5,4)

SELECT CONVERT(INT, 2.9995); -- 2 INT

SELECT ROUND(2.9995, 0); -- 3.0000 NUMERIC(5,4)

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 5

Paul White (8/9/2009)
Jeff Moden (8/9/2009)
No need for rounding if you're going to cast the answer...
Hey Jeff,
Valid point, but let me explain myself: I left the ROUND in for two reasons:
1. To make keep it as close to the original code as possible to make my change clearer
2. To avoid possible subtle changes in behaviour (see not just the values, but the types below)
Consider:
SELECT CONVERT(DECIMAL(9,3), 2.0005); -- 2.001 DECIMAL(9,3)
SELECT ROUND(2.0005, 3); -- 2.0010 NUMERIC(5,4)
SELECT CONVERT(INT, 2.9995); -- 2 INT
SELECT ROUND(2.9995, 0); -- 3.0000 NUMERIC(5,4)
Paul

Agreed but it will all be float in this case because of the 100.0.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 6

Jeff Moden (8/9/2009)
Agreed but it will all be float in this case because of the 100.0.

Will it? 😉

100.0 comes out as NUMERIC(4,1) for me.

ABS(6) comes out as INT.

100.0 / ABS(6) comes out as NUMERIC(15,12).

SELECT A = 100.0,

B = ABS(6),

C = 100.0 / ABS(6)

INTO tempdb.dbo.TestTypes;

execute sp_help 'tempdb.dbo.TestTypes'

DROP TABLE tempdb.dbo.TestTypes;

Data types can be sneaky. 🙂

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 7

Do you know of anything that you can use in SELECT/INTO that will create a float column that isn't specifically cast as a float?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 8

Jeff Moden (8/9/2009)
Do you know of anything that you can use in SELECT/INTO that will create a float column that isn't specifically cast as a float?

Et voila:

SELECT A = 100.0,

B = ABS(6),

C = 100.0 / ABS(6),

D = 5E3 -- Aha!

INTO tempdb.dbo.TestTypes;

execute sp_help 'tempdb.dbo.TestTypes'

DROP TABLE tempdb.dbo.TestTypes;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 9

Heh... kewl. 🙂 In that case, I stand corrected.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)