Failsafe way of converting floats to chars?

Question

Failsafe way of converting floats to chars?

aaa121

SSCertifiable

Points: 5902
More actions
April 17, 2012 at 6:38 am

#267460

I have searched on the forums and looked on BOL but cannot find a definitive solution to this issue.
In a column of data type float I can have either a whole number such as 100000 or a value as 25.9. I need to be able to convert both to a charcter in an efficient way for exporting to a csv file.
To help explain this here are my attempts so far, but none handle both types of value:
declare @table table
(col1 float)
insert into @table (col1)
values (1),
(10),
(100),
(1000),
(10000),
(100000),
(1000000),
(10000000),
(100000000),
(1000000000),
(25.9)
select col1, CAST(col1 as varchar(50)), CAST(CAST(col1 as int) as varchar(50)) from @table

Viewing 7 posts - 1 through 6 (of 6 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Will it harm anything if you end up with some zeroes at the end of the number, after the decimal place? If not, try casting to Decimal instead of Int, then to Varchar.

Edit: e.g., CAST(CAST(col1 AS DECIMAL(18,5)) AS VARCHAR(50))

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ant-Green SSC Guru Points: 113509 More actions · Answer 2

the problem with the int is that it expects whole numbers so you get rounding.

one way would be to do this but every value will then get .00 if its a whole number

CONVERT(VARCHAR,CONVERT(NUMERIC(18,2),col1))

vinu512 SSCoach Points: 15729 More actions · Answer 3

Have you tried it like this??

I'm using SQL Server 2008.

declare @table table

(col1 decimal(18,2) )

insert into @table (col1)

values (1),

(10),

(100),

(1000),

(10000),

(100000),

(1000000),

(10000000),

(100000000),

(1000000000),

(25.9)

select col1, CAST(col1 as varchar(50)), CAST(CAST(col1 as int) as varchar(50)) from @table

The third column is irrelevant in this case.

Hope this is what you are looking for.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

aaa121 SSCertifiable Points: 5902 More actions · Answer 4

Thanks for your replies guys!

This is an outgoing process therefore I need to replace like for like ideally. I did try the casting as decimal route but without knowing how the files are used by the customer I cannot guarentee that this would not cause problems.

I guess if this is the best method I go down this route and carry out a round of testing with the customer.

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

aaa-322853 (4/17/2012)
I guess if this is the best method I go down this route and carry out a round of testing with the customer.

If you have such a rapport with the customer, make the suggestion that they don't use or expect FLOAT values and that they stick with a fixed data-type of one sort or the other.

--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)

GSquared SSC Guru Points: 260824 More actions · Answer 6

aaa-322853 (4/17/2012)
Thanks for your replies guys!
This is an outgoing process therefore I need to replace like for like ideally. I did try the casting as decimal route but without knowing how the files are used by the customer I cannot guarentee that this would not cause problems.
I guess if this is the best method I go down this route and carry out a round of testing with the customer.

Floating point numbers don't translate well to text. If you were transfering via a binary method (linked server or similar), they'd be fine. But the text intermediary step breaks that. Floating point values aren't stored in the same way as decimal numbers, so they just don't translate well to that medium. Makes them faster to perform computations on, but more awkward for other things.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon