Formatting a float in varchar but NOT in scientific notation

I have come across another forum that deals (to a degree) with my issue
but i've got some extra special circumstances.
This below formats a float in Varchar, avoiding scientific notation:
SELECT STR(@.testFloat, 38, 2)
My problem is that I'm never sure how large my scale or precision needs
to be or even if I'm dealing with an integer or a float as I am
enumerating through columns, taking the float/int/varchar value and
putting it into a varchar column in a destination table.
I want to just say "enter into the destination varchar column the exact
same value as what was in the source table (whether it has 4 decimal
places, no decimal places or isn't even numeric).
For example, the above (@.testFloat, 38, 2) is converting my integer
such a 1856 to 1856.00 (argh!!).
anyone?
mikeHi
See other post..
John
"blomm" wrote:
> I have come across another forum that deals (to a degree) with my issue
> but i've got some extra special circumstances.
> This below formats a float in Varchar, avoiding scientific notation:
> SELECT STR(@.testFloat, 38, 2)
> My problem is that I'm never sure how large my scale or precision needs
> to be or even if I'm dealing with an integer or a float as I am
> enumerating through columns, taking the float/int/varchar value and
> putting it into a varchar column in a destination table.
> I want to just say "enter into the destination varchar column the exact
> same value as what was in the source table (whether it has 4 decimal
> places, no decimal places or isn't even numeric).
> For example, the above (@.testFloat, 38, 2) is converting my integer
> such a 1856 to 1856.00 (argh!!).
> anyone?
> mike
>

Formatting a float in varchar but NOT in scientific notation

I have come across another forum that deals (to a degree) with my issue
but i've got some extra special circumstances.
This below formats a float in Varchar, avoiding scientific notation:
SELECT STR(@.testFloat, 38, 2)
My problem is that I'm never sure how large my scale or precision needs
to be or even if I'm dealing with an integer or a float as I am
enumerating through columns, taking the float/int/varchar value and
putting it into a varchar column in a destination table.
I want to just say "enter into the destination varchar column the exact
same value as what was in the source table (whether it has 4 decimal
places, no decimal places or isn't even numeric).
For example, the above (@.testFloat, 38, 2) is converting my integer
such a 1856 to 1856.00 (argh!!).
anyone?
mikeHi
Does CONVERT with a style of 0 work better? e.g.
SELECT CONVERT(varchar(38),col,0) AS DecStr
FROM
( SELECT CAST(1856 as Float) as col
UNION ALL SELECT CAST(1856.09 as Float) ) A
DecStr
---
1856
1856.09
John
"blomm" wrote:
> I have come across another forum that deals (to a degree) with my issue
> but i've got some extra special circumstances.
> This below formats a float in Varchar, avoiding scientific notation:
> SELECT STR(@.testFloat, 38, 2)
> My problem is that I'm never sure how large my scale or precision needs
> to be or even if I'm dealing with an integer or a float as I am
> enumerating through columns, taking the float/int/varchar value and
> putting it into a varchar column in a destination table.
> I want to just say "enter into the destination varchar column the exact
> same value as what was in the source table (whether it has 4 decimal
> places, no decimal places or isn't even numeric).
> For example, the above (@.testFloat, 38, 2) is converting my integer
> such a 1856 to 1856.00 (argh!!).
> anyone?
> mike
>

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

Formatting a float in varchar but NOT in scientific notation

I have come across another forum that deals (to a degree) with my issue
but i've got some extra special circumstances.
This below formats a float in Varchar, avoiding scientific notation:
SELECT STR(@.testFloat, 38, 2)
My problem is that I'm never sure how large my scale or precision needs
to be or even if I'm dealing with an integer or a float as I am
enumerating through columns, taking the float/int/varchar value and
putting it into a varchar column in a destination table.
I want to just say "enter into the destination varchar column the exact
same value as what was in the source table (whether it has 4 decimal
places, no decimal places or isn't even numeric).
For example, the above (@.testFloat, 38, 2) is converting my integer
such a 1856 to 1856.00 (argh!!).
anyone?
mikeHi
Does CONVERT with a style of 0 work better? e.g.
SELECT CONVERT(varchar(38),col,0) AS DecStr
FROM
( SELECT CAST(1856 as Float) as col
UNION ALL SELECT CAST(1856.09 as Float) ) A
DecStr
---
1856
1856.09
John
"blomm" wrote:

> I have come across another forum that deals (to a degree) with my issue
> but i've got some extra special circumstances.
> This below formats a float in Varchar, avoiding scientific notation:
> SELECT STR(@.testFloat, 38, 2)
> My problem is that I'm never sure how large my scale or precision needs
> to be or even if I'm dealing with an integer or a float as I am
> enumerating through columns, taking the float/int/varchar value and
> putting it into a varchar column in a destination table.
> I want to just say "enter into the destination varchar column the exact
> same value as what was in the source table (whether it has 4 decimal
> places, no decimal places or isn't even numeric).
> For example, the above (@.testFloat, 38, 2) is converting my integer
> such a 1856 to 1856.00 (argh!!).
> anyone?
> mike
>

formatting

I have a textbox on a report containing a number I want formatted in
scientific notation. When I bring up the properties of the textbox, there
is a formatting option. I select number and am given a choice of
1.234123e+003. This works great, but I really don't want to show that many
number in the format. I would rather have the number something sensible,
say 1.23e+03. It looks like the custom format option might give the ability
to format the number this way, but I can't find any examples on how to use
it.
Any ideas?
Thanks.They're listed in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp?frame=true.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Leon Chuck Gosslin" <powerb@.inel.gov> wrote in message
news:ekCx1oveEHA.2916@.TK2MSFTNGP12.phx.gbl...
> I have a textbox on a report containing a number I want formatted in
> scientific notation. When I bring up the properties of the textbox, there
> is a formatting option. I select number and am given a choice of
> 1.234123e+003. This works great, but I really don't want to show that
many
> number in the format. I would rather have the number something sensible,
> say 1.23e+03. It looks like the custom format option might give the
ability
> to format the number this way, but I can't find any examples on how to use
> it.
>
> Any ideas?
>
> Thanks.
>
>|||Thanks, man...I owe you a cold beer...
Leon.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:%23YUg5wveEHA.3200@.TK2MSFTNGP09.phx.gbl...
> They're listed in
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp?frame=true.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Leon Chuck Gosslin" <powerb@.inel.gov> wrote in message
> news:ekCx1oveEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > I have a textbox on a report containing a number I want formatted in
> > scientific notation. When I bring up the properties of the textbox,
there
> > is a formatting option. I select number and am given a choice of
> > 1.234123e+003. This works great, but I really don't want to show that
> many
> > number in the format. I would rather have the number something
sensible,
> > say 1.23e+03. It looks like the custom format option might give the
> ability
> > to format the number this way, but I can't find any examples on how to
use
> > it.
> >
> >
> >
> > Any ideas?
> >
> >
> >
> > Thanks.
> >
> >
> >
> >
>

format output as scientific notation (was "sql 2000 question")

SELECT membername, outputval
case when choice = 0 then outputval else null end as outputval
from MyDatabase
group by membername, outputval

how to format outputval:
if outputval < 40000
format outputval as:
5 - 5.78 - 6.9 - 6,778 - 4,567.8 - 12,456.78 - etc.
if outputval >= 40000
format it as a scientific.I would strongly suggest that you do the formatting at either the web server (using PHP), or better yet at the client (possibly using Javascript). This would allow you to use locale specific information that isn't usually available to SQL Server.

If you really have to format the output at the SQL Server, it could be done using a SQL UDF that called xp_sprintf or other related tools. This means that every query will need to be formatted using the locale of your SQL Server, which is usually a huge problem for application globalization.

-PatP