Author |
Topic |
shanmugaraj
Posting Yak Master
219 Posts |
Posted - 2011-09-09 : 02:01:26
|
Hi,I want to convert the below string values to numericthe problem is since the "." Is there I need to be identified as decimalthe destination column datatype is numeric (12,3)SaleVolume------------6.73317454.098141.22385-578202374010.420392.36408-28048Select CAST(SaleVolume AS NUMERIC(17,2)) Select CONVERT(NUMERIC(17,2),AmtTwo )Error converting data type varchar to numeric.:( I am not able to use the cast ,convert function :(Thanks for help in advanceTHANKSSHANMUGARAJnshanmugaraj@gmail.com |
|
Kristen
Test
22859 Posts |
Posted - 2011-09-09 : 02:11:12
|
"I am not able to use the convert function"Please clarify why not?i.e.SELECT CONVERT(numeric(12,3), SaleVolume) |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-09 : 02:13:06
|
"Error converting data type varchar to numeric."You probably have non-numeric data in your column.SELECT SaleVolume, PKColumn1, ...FROM YourTableWHERE SaleVolume LIKE '%[^-.0-9]%' OR IsNumeric(SaleVolume) <> 1 |
|
|
shanmugaraj
Posting Yak Master
219 Posts |
Posted - 2011-09-09 : 02:15:34
|
Since the varchar have "."seems the sql is not able to find the exact convertion..i am not sure on this since i am new to sql :(what clarificaiton are you looking for.. plse helpTHANKSSHANMUGARAJnshanmugaraj@gmail.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-09 : 02:19:15
|
nope . is not a problem so far as you're converting to numeric. seeselect convert(numeric(15,2),'2133244441212.2131423414')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
shanmugaraj
Posting Yak Master
219 Posts |
Posted - 2011-09-09 : 02:20:11
|
I will not be able to filter or remove any columns. the data from flat file is something like..6.73317454.098141.22385-57820and i need them to coverted into numeric...i tried the reply logic by Kristen, i am getting same errorTHANKSSHANMUGARAJnshanmugaraj@gmail.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-09 : 02:20:28
|
what does below return?select * from table where isnumeric(field) = 0 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-09 : 02:20:53
|
"what clarificaiton are you looking for"You have since provided it by editing your original post, thank you.Run my query above to find the data that is not compatible for conversion. It has nothing to do with the "." in the data. |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-09 : 02:22:26
|
"i tried the reply logic by Kristen, i am getting same error"Post the exact query you used please |
|
|
shanmugaraj
Posting Yak Master
219 Posts |
Posted - 2011-09-09 : 02:23:11
|
select * from table where isnumeric(field) = 0returns Msg 8114, Level 16, State 5, Line 3Error converting data type varchar to numeric.THANKSSHANMUGARAJnshanmugaraj@gmail.com |
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2011-09-09 : 02:26:46
|
may be there is some other issue you are having as from VARCHAR converting to NUmeric is possible.DECLARE @Value VARCHAR(10)SET @value=45SELECT CAST(45 as NUMERIC(12,3))iF theRe iS a wAy iN tHen theRe iS a wAy oUt.. |
|
|
shanmugaraj
Posting Yak Master
219 Posts |
Posted - 2011-09-09 : 02:34:03
|
i dont know , its not working :(what do u want from the data source column ?select convert(numeric(15,2),'2133244441212.2131423414')is working fine for me.. but when i use my column as input, it is giving same errorTHANKSSHANMUGARAJnshanmugaraj@gmail.com |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-09 : 02:34:38
|
"elect * from table where isnumeric(field) = 0returns Msg 8114, Level 16, State 5, Line 3Error converting data type varchar to numeric."Well I wouldn't have believed that was possible! But I would like to know what value will cause IsNumeric() function to raise error please, when you find it.Try justSELECT SaleVolume, PKColumn1, ...FROM YourTableWHERE SaleVolume LIKE '%[^-.0-9]%' AND SaleVolume IS NOT NULL |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-09 : 02:35:00
|
quote: Originally posted by shanmugaraj select * from table where isnumeric(field) = 0returns Msg 8114, Level 16, State 5, Line 3Error converting data type varchar to numeric.THANKSSHANMUGARAJnshanmugaraj@gmail.com
what about this?select * from table where not field like '%[^0-9]%'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-09 : 02:35:22
|
If that fails please post your EXACT query. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-09 : 02:48:55
|
quote: Originally posted by visakh16
quote: Originally posted by shanmugaraj select * from table where isnumeric(field) = 0returns Msg 8114, Level 16, State 5, Line 3Error converting data type varchar to numeric.THANKSSHANMUGARAJnshanmugaraj@gmail.com
what about this?select * from table where field like '%[^0-9]%'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|