Formatting a float in varchar but NOT in scientific notation

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
DaveTry 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" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0312130327.4482a7a@.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|||STR() function might help you.

SELECT STR(123.45, 6, 1)

Check BOL.

"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0312130327.4482a7a@.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|||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