changing float to varchar using cast 1

[557]

i need to concatenate a varchar field with a float field. for that i need to convert the float field to varchar using cast as follows

select rtrim(sgln_description) + cast (round(sgln_blockamt,3) as varchar(30)) from table1


this works fine but when the values stored in float are very large, for eg:-

1000000000000.0 and 999999999999.0 are converted to 1e+011 when i use cast.

i need it to be exactly what is given in the float field without any exponential notation. i tried removing the round function but still the result is not different. can anyone please help me get the concatenated string without any change in the number?

 

[donutman]

Try this:

[Blue]SELECT[/Blue]  [Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]sgln_description[Gray])[/Gray] [Gray]+[/Gray] 
        [Fuchsia]Cast[/Fuchsia][Gray]([/Gray][Fuchsia]Cast[/Fuchsia][Gray]([/Gray]sgln_blockamt [Blue]AS[/Blue] [Blue]numeric[/Blue][Gray]([/Gray]26[Gray],[/Gray]3[Gray])[/Gray][Gray])[/Gray] [Blue]AS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]30[Gray])[/Gray][Gray])[/Gray] 
   [Blue]FROM[/Blue] table1

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[557]

thanks donutman. it works.

but before you replied, i had tried

select rtrim(sgln_description) + cast (round(cast(sgln_blockamt as money),3) as varchar(30)) from table1

and it worked fine.

do you find any hidden problems with what i've tried?

 

[donutman]

NP

 

[ESquared]

557,

The reason that you are getting the 'wrong' answer is because float is an inexact data type. If you must have *exact* amounts, then you should use decimal in your tables instead of float. The value you got when using cast straight to varchar was a faithful representation of the 'exact' float value, believe it or not.

By converting to decimal first you are actually doing rounding. If that is acceptable, then ignore what I've said. But if you really want to store exact values then stop using float entirely!

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[557]

in my tables, if i change all floats to decimals, will it create any problem in any of the values?

 

[ESquared]

It will only create a problem in the values if there is *already* a problem in the values. Better to do the conversion now so you can find any problems rather than wait until later...

But, chances are low that there will be any discrepancies. Just be sure to pick a sensible decimal type.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[557]

sensible decimal type means what????

 

[ESquared]

if all your data only has 2 decimal points, it doesn't make sense to use a decimal type like decimal(20,10) with 10 decimal places. Instead, you'd want to use something like (12,2). Look up the decimal data type in BOL to see storage requirements for the total number of digits.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)