conversion from decimal to varchar

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

conversion from decimal to varchar

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2009-08-14 : 07:05:05

Hi All,
I have the foll. set of data:


declare @table1 table
( col1 float
)
insert into @table1
select 0.234234234 union all
select 24 union all
select 2270377 union all
select 3333 union all
select 0.59

select * from @table1
select convert(varchar(100),col1) from @table1

there is an issue for value - 2270377 . it is converted to exponential format.
Is there a way i can show as it is.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-08-14 : 07:53:59

[code]
select
convert(varchar(100),col1)
,LTRIM(RTRIM(STR(ROUND(col1,9),20,2)))
from @table1
[/code]
HOWEVER, it will add trailing zeros, and will only go to 16 decimal places. Not a solution, but perhaps a shot in the right direction.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

zion99
Posting Yak Master

141 Posts

Posted - 2009-08-14 : 07:59:34

So, isn't there a way to stop this automatic converstion to exponential format ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-14 : 08:11:18

Not when using FLOAT datatype.

See Books Online here
http://msdn.microsoft.com/en-us/library/ms187928.aspx

quote:
When expression is float or real, style can be one of the values shown in the following table. Other values are processed as 0.

Value Output
0 (default)
A maximum of 6 digits. Use in scientific notation, when appropriate.

1
Always 8 digits. Always use in scientific notation.

2
Always 16 digits. Always use in scientific notation.

126, 128, 129
Included for legacy reasons and might be deprecated in a future release.

N 56°04'39.26"
E 12°55'05.63"

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-14 : 10:30:19

there is no reason to be using FLOAT data type for any of the values in your example. in most apps, there's no reason to use it at all for that matter.


declare @table1 table
( col1 decimal(16, 9)
)
insert into @table1
select 0.234234234 union all
select 24 union all
select 2270377 union all
select 3333 union all
select 0.59

select * from @table1
select convert(varchar(100),col1) from @table1

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-08-14 : 17:47:40

You might consider converting it to an intermediate type first:

select convert(varchar(100), convert(int, col1))

or

select convert(varchar(100), convert(decimal(16, 8), col1))

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-14 : 20:57:50

quote:
Originally posted by Bustaz Kool

You might consider converting it to an intermediate type first:

select convert(varchar(100), convert(int, col1))

or

select convert(varchar(100), convert(decimal(16, 8), col1))

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

but why not use appropriate data type in the 1st place...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources