Converting Float value to varchar

Question

Post reply

Converting Float value to varchar

Adam Angelini

SSCrazy Eights

Points: 8548
More actions
October 15, 2007 at 12:57 pm

#180457

say you run this sql:
DECLARE @fl AS FLOAT
SET @fl = 789512385
SELECT CAST(@fl AS VARCHAR(50))
it prints out 7.89512e+008
I need to get this value converted to varchar without loosing its formatting because I need to put a float column into a varchar column, but we want the values to look like 789512385, not 7.89512e+008
I've tried using the different "styles" in the convert function, but none got me to where I need to be.
Anyone know how to handle this?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i'm not sure why it jumps to scientific notation...wierd.

this works, it's simply a double cast/do the same with convert:

DECLARE @fl AS FLOAT

SET @fl = 789512385

SELECT cast(CAST(@fl AS bigint) AS varchar(50))

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!

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 2

That works - thanks a bunch. seems like I tried that one, but I must have had something wrong:)

noeld SSC Guru Points: 96590 More actions · Answer 3

The correct "casting" function should be STR.

DECLARE @fl AS FLOAT

SET @fl = 789512385

SELECT str(@fl,9,0)

* Noel

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 4

DECLARE @fl AS FLOAT

Just change float to decimal (or numeric). Internally, floats and doubles are maintained in an entirely different format from decimals and numerics. That's why you don't get the rounding (and apparently conversion) problems with decimal/numeric.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms