| Author |
Topic |
|
masterslave
Starting Member
22 Posts |
 Posted - 2008-01-21 : 19:33:28
|
| I've got some values stored in nvachar(255) field stored by mistake as scientific notation (eg 7.5013e+006 instead of 7501301) and I need to convert and update the field with normal entry, not scientific notation. Is there a way to do that? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-21 : 19:53:20
|
| Try this:update table set col = convert(nvarchar(255), convert(int, col)) where ... |
 |
|
|
masterslave
Starting Member
22 Posts |
Posted - 2008-01-21 : 20:13:48
|
| Unfortunately, that gives me an error:Syntax error converting the nvarchar value '9.1577e+006' to a column of data type int. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-21 : 20:39:17
|
| Ok, should use float type. May need save convert(float, col)) to temp table then put back. |
|
|
|
masterslave
Starting Member
22 Posts |
Posted - 2008-01-21 : 21:14:00
|
| Thanks rmiao, but when it still outputs the data when I use it as part of SELECT statement: convert(nvarchar(255), convert(float, b.baseitem)) |
|
|
|
masterslave
Starting Member
22 Posts |
Posted - 2008-01-21 : 21:21:54
|
| Sorry, meant to say outputs the data without any changes, with scientific formatting applied. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-21 : 22:30:57
|
| Did you do this?select convert(float, b.baseitem) from table where ... |
|
|
|
masterslave
Starting Member
22 Posts |
Posted - 2008-01-21 : 23:27:54
|
| When I do that convert(float, b.baseitem), I get output of 9157700.0 where the b.baseitem is 9.1577e+006, and it should actually be 9157702 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-22 : 02:13:33
|
It never can be! The number is stored as 9157700! That is what 9.1577e+006 means!
E 12°55'05.25"N 56°04'39.16" |
|
|
|
masterslave
Starting Member
22 Posts |
Posted - 2008-01-22 : 06:23:07
|
| Sorry Peso, but it's actually stored as 9.1577e+006 in a varchar field! I had data imported incorrectly from an Excel spreadsheet so it got saved to the database as string in scientific notation... And that's what I'd like to resolve! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-22 : 07:10:40
|
9.1577e+006 can NEVER be evaluated as 9157702.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2008-01-22 : 07:15:37
|
| what has happened is the excel conversion rounded the data off - if it was to be 100% accurate (but then converting to scientific notation would be useless as it would require more space than normal decimal form) it would have it as 9.157702e+006.I think excel uses scientific notation to round off large numbers for easier display - I think if the column is made wider it can accomodate larger numbers before having to use scientific notation again.Duane. |
|
|
|
|
Convert from scientific notation