By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,865 Members | 1,453 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,865 IT Pros & Developers. It's quick & easy.

Formatting a float in varchar but NOT in scientific notation

P: n/a
I'm trying to find a way to format a FLOAT variable into a varchar in
SQL Server 2000 but using CAST/CONVERT I can only get scientific
notation i.e. 1e+006 instead of 1000000 which isn't really what I
wanted.

Preferably the varchar would display the number to 2 decimal places
but I'd settle for integers only as this conversion isn't business
critical and is a nice to have for background information.

Casting to MONEY or NUMERIC before converting to a varchar works fine
for most cases but of course runs the risk of arithmetic overflow if
the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
knows of an easy way to test whether overflow will occur and therefore
to know not to convert it then that would be an option.

I appreciate SQL Server isn't great at formatting and it would be far
easier in the client code but code this is being performed as a
description of a very simple calculation in a trigger, all stored to
the database on the server side so there's no opportunity for client
intervention.

Example code:

declare @testFloat float
select @testFloat = 1000000.12

select convert(varchar(100),@testFloat) -- gives 1e+006
select cast(@testFloat as varchar(100)) -- gives 1e+006
select convert(varchar(100),cast(@testFloat as money)) -- gives
1000000.12

select @testFloat = 12345678905345633453453624453453524.123

select convert(varchar(100),cast(@testFloat as money)) -- gives
arithmetic overflow error
select convert(varchar(100),cast(@testFloat as numeric)) -- gives
arithmetic overflow error

Any suggestions welcome...

Cheers
Dave
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Try specifying the desired precision and scale on your decimal/numeric
declaration:

SELECT CONVERT(varchar(100), CAST(@testFloat AS decimal(38,2)))

--
Hope this helps.

Dan Guzman
SQL Server MVP
"David Sharp" <da**@daveandcaz.freeserve.co.uk> wrote in message
news:ca*************************@posting.google.co m...
I'm trying to find a way to format a FLOAT variable into a varchar in
SQL Server 2000 but using CAST/CONVERT I can only get scientific
notation i.e. 1e+006 instead of 1000000 which isn't really what I
wanted.

Preferably the varchar would display the number to 2 decimal places
but I'd settle for integers only as this conversion isn't business
critical and is a nice to have for background information.

Casting to MONEY or NUMERIC before converting to a varchar works fine
for most cases but of course runs the risk of arithmetic overflow if
the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
knows of an easy way to test whether overflow will occur and therefore
to know not to convert it then that would be an option.

I appreciate SQL Server isn't great at formatting and it would be far
easier in the client code but code this is being performed as a
description of a very simple calculation in a trigger, all stored to
the database on the server side so there's no opportunity for client
intervention.

Example code:

declare @testFloat float
select @testFloat = 1000000.12

select convert(varchar(100),@testFloat) -- gives 1e+006
select cast(@testFloat as varchar(100)) -- gives 1e+006
select convert(varchar(100),cast(@testFloat as money)) -- gives
1000000.12

select @testFloat = 12345678905345633453453624453453524.123

select convert(varchar(100),cast(@testFloat as money)) -- gives
arithmetic overflow error
select convert(varchar(100),cast(@testFloat as numeric)) -- gives
arithmetic overflow error

Any suggestions welcome...

Cheers
Dave

Jul 20 '05 #2

P: n/a
STR() function might help you.

SELECT STR(123.45, 6, 1)

Check BOL.
"David Sharp" <da**@daveandcaz.freeserve.co.uk> wrote in message
news:ca*************************@posting.google.co m...
I'm trying to find a way to format a FLOAT variable into a varchar in
SQL Server 2000 but using CAST/CONVERT I can only get scientific
notation i.e. 1e+006 instead of 1000000 which isn't really what I
wanted.

Preferably the varchar would display the number to 2 decimal places
but I'd settle for integers only as this conversion isn't business
critical and is a nice to have for background information.

Casting to MONEY or NUMERIC before converting to a varchar works fine
for most cases but of course runs the risk of arithmetic overflow if
the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
knows of an easy way to test whether overflow will occur and therefore
to know not to convert it then that would be an option.

I appreciate SQL Server isn't great at formatting and it would be far
easier in the client code but code this is being performed as a
description of a very simple calculation in a trigger, all stored to
the database on the server side so there's no opportunity for client
intervention.

Example code:

declare @testFloat float
select @testFloat = 1000000.12

select convert(varchar(100),@testFloat) -- gives 1e+006
select cast(@testFloat as varchar(100)) -- gives 1e+006
select convert(varchar(100),cast(@testFloat as money)) -- gives
1000000.12

select @testFloat = 12345678905345633453453624453453524.123

select convert(varchar(100),cast(@testFloat as money)) -- gives
arithmetic overflow error
select convert(varchar(100),cast(@testFloat as numeric)) -- gives
arithmetic overflow error

Any suggestions welcome...

Cheers
Dave

Jul 20 '05 #3

P: n/a
Dan and Igor, both examples worked great, thanks very much.

SELECT CONVERT(varchar(100), CAST(@testFloat AS decimal(38,2)))
SELECT STR(@testFloat, 38, 2)

Cheers
Dave
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.