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.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2006-09-06 : 11:27:27
|
| This one I am sure is easy to some of you, but I had hard time to find a good way to get around.I have a set of data in the type of varchar nullable, and they look like Null, 2.62500, 3.952800, 5.000000, and 10.000000.And the end results should be:(empty string for the null), 2.625, 3.9528, 5, and 10What are the elegant ways to achieve this? I did not find any string function that will help. I am afraid it has to be converted to decimal(?) then round(?) function then convert back?I am stuck with data type. Thanks! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-06 : 11:44:29
|
| Use the FLOAT, Hummer.Peter LarssonHelsingborg, Sweden |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-09-06 : 12:09:58
|
| If you want to store numbers then why do you use varchar datatype? Use proper decimal or float datatypeAlso if you use front end application, use format function thereMadhivananFailing to plan is Planning to fail |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-06 : 12:20:48
|
| [code]declare @test table(n varchar(20))insert @testselect Null union allselect '2.62500' union allselect '3.952800' union allselect '5.000000' union allselect '10.000000'select n, CAST(CAST(n AS FLOAT) AS VARCHAR)from @test[/code]Peter LarssonHelsingborg, Sweden |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2006-09-06 : 12:24:50
|
| Thanks Peso and Madhivanan!I know the normal rules, i.e. let the front end do the formating and stored the data in the right data type.Unfortunately, I have no control over either one.I will convert them into float and see what I get. |
|
|
|
|
|
|
|
|
Format numbers