Formatting a float in varchar but NOT in scientific notation

[David Sharp]

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

[Dan Guzman]

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

[Igor Raytsin]

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

[David Sharp]

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