Question: float display as character

Question

Post reply

Question: float display as character

Sharon S

SSC Enthusiast

Points: 125
More actions
September 8, 2010 at 7:37 pm

#225941

Hi all,
I am not understanding why there is a display difference in the decimal placement for these examples. Anyone know why? I am in need to display the float result as character data in the same manner as the float does (as the select @ReturnValue does)
declare @returnvalue float
set @ReturnValue = ((6840844991.81 + 113664947.42) - (6947944500.34 + 2417617.57)) / (6947944500.34 + 2417617.57)
select @ReturnValue --this is the correct calculated float number
select STR(@ReturnValue,10,2) --this displays incorrectly
select CONVERT(char(25), @ReturnValue) --this displays incorrectly
select CONVERT(decimal(38,38), @ReturnValue) --this displays incorrectly
select cast(@ReturnValue as varchar(25)) --this displays incorrectly
Displays:
----------------------------------------------------
5.9677772893460001E-4
----------
0.00
-------------------------
0.000596778
----------------------------------------
.00059677772893460000000000000000000000
-------------------------
0.000596778

Viewing 14 posts - 1 through 13 (of 13 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Will this do?

declare @returnvalue float

set @ReturnValue = ((6840844991.81 + 113664947.42) - (6947944500.34 + 2417617.57)) / (6947944500.34 + 2417617.57)

select @ReturnValue --this is the correct calculated float number

SELECT UPPER(CONVERT(VARCHAR(30),@ReturnValue,2))

--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)
Intro to Tally Tables and Functions

Lowell SSC Guru Points: 323482 More actions · Answer 2

Jeff Moden (9/8/2010)
Will this do?
SELECT UPPER(CONVERT(VARCHAR(30),@ReturnValue,2))
[/code]

I know there is usually a reason behind everything in your posts , Jeff...I love reading your posts specifically because I learn something (and I'm not behind the curve, as it were, either) but why UPPER a numerical value? what did i miss?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Lowell (9/9/2010)
Jeff Moden (9/8/2010)
Will this do?
SELECT UPPER(CONVERT(VARCHAR(30),@ReturnValue,2))
[/code]
I know there is usually a reason behind everything in your posts , Jeff...I love reading your posts specifically because I learn something (and I'm not behind the curve, as it were, either) but why UPPER a numerical value? what did i miss?

Suspect it's the "e-004", which should be "E-004"

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

declare @returnvalue float

set @ReturnValue = ((6840844991.81 + 113664947.42) - (6947944500.34 + 2417617.57)) / (6947944500.34 + 2417617.57)

SELECT @ReturnValue --this is the correct calculated float number

-- truncate to 16dp

SELECT CONVERT(DECIMAL(26,16),@ReturnValue)

-- Use STR on remainder

SELECT STR(CONVERT(DECIMAL(26,16),@ReturnValue),26,16)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Lowell (9/9/2010)
Jeff Moden (9/8/2010)
Will this do?
SELECT UPPER(CONVERT(VARCHAR(30),@ReturnValue,2))
[/code]
I know there is usually a reason behind everything in your posts , Jeff...I love reading your posts specifically because I learn something (and I'm not behind the curve, as it were, either) but why UPPER a numerical value? what did i miss?

It's just to convert the "e" notation to "E".

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

skcadavre (9/9/2010)
Lowell (9/9/2010)
Jeff Moden (9/8/2010)
Will this do?
SELECT UPPER(CONVERT(VARCHAR(30),@ReturnValue,2))
[/code]
I know there is usually a reason behind everything in your posts , Jeff...I love reading your posts specifically because I learn something (and I'm not behind the curve, as it were, either) but why UPPER a numerical value? what did i miss?
Suspect it's the "e-004", which should be "E-004"

Spot on. 🙂

--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)
Intro to Tally Tables and Functions

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

Chris Morris-439714 (9/9/2010)
declare @returnvalue float set @ReturnValue = ((6840844991.81 + 113664947.42) - (6947944500.34 + 2417617.57)) / (6947944500.34 + 2417617.57) SELECT @ReturnValue --this is the correct calculated float number -- truncate to 16dp SELECT CONVERT(DECIMAL(26,16),@ReturnValue) -- Use STR on remainder SELECT STR(CONVERT(DECIMAL(26,16),@ReturnValue),26,16)

Good job, Chris.... it's just missing the "Engineering Notation" or "E" notation that the op is looking for.

As a side bar, the "2" option I used in the conversion is the 16 byte option for "E" notation. If I'd used "1", it would have returned the 8 byte option, instead. Using "0" would have produced only 6 digits and the "E" notation would only appear if necessary instead of being forced like the other two options are. This only works when converting FLOAT to a character based datatype such as VARCHAR. If you do the same thing with a MONEY datatype, you get various renditions including some with thousands indicators (commas in the U.S.).

--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)
Intro to Tally Tables and Functions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Jeff Moden (9/9/2010)
As a side bar, the "2" option I used in the conversion is the 16 byte option for "E" notation. If I'd used "1", it would have returned the 8 byte option, instead. Using "0" would have produced only 6 digits and the "E" notation would only appear if necessary instead of being forced like the other two options are. This only works when converting FLOAT to a character based datatype such as VARCHAR. If you do the same thing with a MONEY datatype, you get various renditions including some with thousands indicators (commas in the U.S.).

Thanks for pointing this out, Jeff. The CAST & CONVERT section of BOL for 2k8 is bigger than the whole of BOL for v6.5 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sharon S SSC Enthusiast Points: 125 More actions · Answer 9

Jeff and all,

Thanks for posting this response - it did the trick! SELECT UPPER(CONVERT(VARCHAR(30),@ReturnValue,2))

However, I don't understand why. Would you mind explaining if you have the time?

Thanks so much,

Sharon

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Sharon S (9/9/2010)
Jeff and all,
Thanks for posting this response - it did the trick! SELECT UPPER(CONVERT(VARCHAR(30),@ReturnValue,2))
However, I don't understand why. Would you mind explaining if you have the time?
Thanks so much,
Sharon

I already explained... please see the stuff I wrote below the code in my previous post.

--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)
Intro to Tally Tables and Functions

Sharon S SSC Enthusiast Points: 125 More actions · Answer 11

Sharon S

SSC Enthusiast

Points: 125

September 9, 2010 at 10:13 pm

#1218995

I see it now - thx again...

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

Sharon S (9/9/2010)
I see it now - thx again...

You bet... thanks for the feedback.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

Chris Morris-439714 (9/9/2010)
Jeff Moden (9/9/2010)
As a side bar, the "2" option I used in the conversion is the 16 byte option for "E" notation. If I'd used "1", it would have returned the 8 byte option, instead. Using "0" would have produced only 6 digits and the "E" notation would only appear if necessary instead of being forced like the other two options are. This only works when converting FLOAT to a character based datatype such as VARCHAR. If you do the same thing with a MONEY datatype, you get various renditions including some with thousands indicators (commas in the U.S.).
Thanks for pointing this out, Jeff. The CAST & CONVERT section of BOL for 2k8 is bigger than the whole of BOL for v6.5 😀

Heh... agreed. Isn't it funny that although "we" preach that formatting should be done in the GUI, that there's so much formatting that can be done in SQL? CAST, CONVERT, STR, DATENAME, etc, etc.

--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)
Intro to Tally Tables and Functions