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 |
|
dnf999
Constraint Violating Yak Guru
253 Posts |
 Posted - 2009-10-27 : 04:46:48
|
| HiI have been provided with a database where a table has been imported from Excel and Access and contains values such as:1.00059e+0074.91325e+006How can I convert these using SQL code to the correct numeric values:1.00059e+007 -> 100059004.91325e+006 -> 4913250Please note I do not have access to the source data and am required to perform this on the data in the SQL table.Thanks! |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-10-27 : 04:49:12
|
| select cast(1.00059e+007 as int)Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
 |
|
|
dnf999
Constraint Violating Yak Guru
253 Posts |
Posted - 2009-10-27 : 05:15:38
|
| Sorry I should have been a bit more specific. The [Amount] field is nvarchar and can contain unclean data. So when I try to run the below query I get the following error.SELECT [Amount], ltrim(rtrim(replace([Amount],'0 ; ',''))), cast(ltrim(rtrim(replace([Amount],'0 ; ',''))) as int)FROM TABLE_1WHERE [Amount] = '0 ; 1.00002e+007'Msg 245, Level 16, State 1, Line 1Conversion failed when converting the nvarchar value '1.00002e+007' to data type int.As you can see the amount can have strange values in, for which I need to convert.Thanks |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-10-27 : 06:10:53
|
| cast(cast(ltrim(rtrim(replace([Amount],'0 ; ',''))) as float) as int)MadhivananFailing to plan is Planning to fail |
|
|
|
dnf999
Constraint Violating Yak Guru
253 Posts |
Posted - 2009-10-27 : 08:40:21
|
| Thank you!!That's worked a treat. |
|
|
|
|
|
|
|
|
Convert value to numeric