Author |
Topic |
johnmitch38
Starting Member
9 Posts |
Posted - 2013-11-14 : 09:36:08
|
Afternoon all,I need to convert a column of data from alpha numeric to integer.I am only querying the tables i.e. i don't have access to actually change the data tables themselves.CAST or CONVERT throws up an error. Are there any other commands i can use at the query stage?The data I need to convert is always actually a number. i.e. even though it is recognised as alpha numeric, the figure is a number.I just need it to be converted to an integer so i can SUM it etc.Thanks,JM |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-11-14 : 10:01:49
|
>>CAST or CONVERT throws up an errorPost the error.>>The data I need to convert is always actually a numberIt sounds like this is not true. Either that or the number violates the range or precision constraints of INT.EDIT:you may be able to find the offending value(s) with this although it is not fool proof:select <col> from <yourTable> where isnumeric(<col>) = 0Be One with the OptimizerTG |
|
|
johnmitch38
Starting Member
9 Posts |
Posted - 2013-11-18 : 09:47:47
|
>>CAST or CONVERT throws up an errorPost the error.>>The error says "Cannot convert 150.00 to a int">>The data I need to convert is always actually a numberIt sounds like this is not true. Either that or the number violates the range or precision constraints of INT.>>What I mean is that every value in the relevant column looks like a number. i.e. there is never any text. The only thing resembling text is a decimal. Excel however i recognising these "numbers" as text.Thanks,JM |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-18 : 09:49:39
|
Are you sure there're no unprintable characters present?I'd a similar issue once and reason was presence of hard space characters.Try this tooSELECT CAST(REPLACE(YourintegerField,CHAR(160),'') AS int)FROM Table ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-11-18 : 10:06:16
|
If the data type of the column not some type of numeric data type but rather a character data type then this will throw that same error:select convert(int, '150.00')However, if you go through a more precise data type then to int it should work:select convert(int, convert(money, '150.00'))Be One with the OptimizerTG |
|
|
johnmitch38
Starting Member
9 Posts |
Posted - 2013-11-19 : 08:46:35
|
Visakh16 - I'm still getting the same error where it says "cannot convert '150.00' to a int"TG - I assume your way would only convert the number if it happens to be '150.00'? I need a way where it converts every "number" in the column to an integer no matter what the number may be.Thanks,JM |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-19 : 09:07:40
|
quote: Originally posted by johnmitch38 Visakh16 - I'm still getting the same error where it says "cannot convert '150.00' to a int"TG - I assume your way would only convert the number if it happens to be '150.00'? I need a way where it converts every "number" in the column to an integer no matter what the number may be.Thanks,JM
for that first thing you need to confirm is whther it contains numbers and only numbers..Unless you've some other non numeric characters present it wont throw the conversion error.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-11-19 : 10:06:07
|
quote: Originally posted by johnmitch38 TG - I assume your way would only convert the number if it happens to be '150.00'? I need a way where it converts every "number" in the column to an integer no matter what the number may be.
I supplied those statements with your hard coded example so you could actually run it and see that the first through the exact same error you reported where as the second did not. Once you satisfied of that you can replace the hard coded '150.00' with your column name.select convert(int, convert(money, <yourColumn>))from <yourTable>where ....Now, what Visakh and I both have said is still true. If there are any non-numeric values in that column at all you'll still get a conversion error.Be One with the OptimizerTG |
|
|
johnmitch38
Starting Member
9 Posts |
Posted - 2013-11-20 : 05:40:25
|
Brilliant, that worked a treat.Many thanks.JM |
|
|
|