473,889 Members | 1,391 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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),@testFloa t) -- gives 1e+006
select cast(@testFloat as varchar(100)) -- gives 1e+006
select convert(varchar (100),cast(@tes tFloat as money)) -- gives
1000000.12

select @testFloat = 123456789053456 334534536244534 53524.123

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

Any suggestions welcome...

Cheers
Dave
Jul 20 '05 #1
3 146829
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**@daveandca z.freeserve.co. uk> wrote in message
news:ca******** *************** **@posting.goog le.com...
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),@testFloa t) -- gives 1e+006
select cast(@testFloat as varchar(100)) -- gives 1e+006
select convert(varchar (100),cast(@tes tFloat as money)) -- gives
1000000.12

select @testFloat = 123456789053456 334534536244534 53524.123

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

Any suggestions welcome...

Cheers
Dave

Jul 20 '05 #2
STR() function might help you.

SELECT STR(123.45, 6, 1)

Check BOL.
"David Sharp" <da**@daveandca z.freeserve.co. uk> wrote in message
news:ca******** *************** **@posting.goog le.com...
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),@testFloa t) -- gives 1e+006
select cast(@testFloat as varchar(100)) -- gives 1e+006
select convert(varchar (100),cast(@tes tFloat as money)) -- gives
1000000.12

select @testFloat = 123456789053456 334534536244534 53524.123

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

Any suggestions welcome...

Cheers
Dave

Jul 20 '05 #3
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
8082
by: Skeleton Man | last post by:
Hi, I have a script that generates numbers beyond the range of it, and so it's throwing them out as scientfic notation (e.g. 5E+10 instead of 50,000,000,000). The only way I've found of avoiding this is by using bc*() functions in place of normal operators (e.g. bcmul() instead of '*'). Surely there's a way to simply convert a number in scientific notation into a normal integer ? Regards,
5
17671
by: Dennis Myrén | last post by:
Hi. Is there a way to make sure that float, double and decimal data types never will be presented in a scientific notation? I have tried to round(Math.Round) float's to 7 decimals, double's to 15 and decimals to 28 decimals, but that does not help. And the System.Globalization.NumberFormatInfo class does not seem to provide a such function.
0
1718
by: Greg | last post by:
I am working on an application that requires working with numbers in scientific notation. I am using SqlServer as the database and I have created strongly typed data adapters and datasets. The numbers are defined as numeric in the SqlServer Database and are bound to text boxes in the app. In the dataset xml these numbers are defined as decimal. I am using the binding method Format to display the data in scientific notation and Parse to...
2
4420
by: masoud bayan | last post by:
I have some values in type of double such 0.00009 , 0.0000007, when I want to show them in a text box and convert them to string they are changed to scientific notation while I want to show them as they are. I tried to use tostring("f") but it needs to have a precision number after *f* which will cause always have fix number of characters (for example if use f10 then 0.09 is converted to 0.09000000 while I need to show it as 0.09). do...
7
10922
by: Dustan | last post by:
How can I get a number into scientific notation? I have a preference for the format '1 E 50' (as an example), but if it's well known, it works.
2
7291
by: Ryan Liu | last post by:
In C#, for a large float (9 digitals), how can I disable Scientific notation. When it auto convert to Scientific notation, I lost accuracy. Thanks a lot! Ryan
2
7042
by: Greg | last post by:
I am working on an application that requires working with numbers in scientific notation. I am using SqlServer as the database and I have created strongly typed data adapters and datasets. The numbers are defined as numeric in the SqlServer Database and are bound to text boxes in the app. In the dataset's xml these numbers are defined as decimal. I am using the binding method Format to display the data in scientific notation and Parse to...
1
27703
by: dmitri | last post by:
I am importing a bunch of data which is all in varchar format which I cast into the relevant type on transfer to the production table. One of these columns contains decimals, but some of them are occasionally given in scientific notation (3.48E-02). I am using CAST (v1 AS decimal(18, 13)) to do the conversion, but that seems to be unable to handle the scientific notation. Does anyone know a way around this?
5
3639
by: ChopperDave | last post by:
Hi, guys I am a noob at programming full stop and am having to take a unit of it at uni. I currently have an easy part to this assignment and a hard part. I am finishing up the easy part currently which was prompt the user for 10 float values, store them in an array, ouput all 10 but only to 4 decimal places, find the maximum and then output this in scientific notation. I have done everything but have failed to learn how do print it in...
0
9961
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9805
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10784
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10439
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9602
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7991
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5825
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6021
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3252
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.