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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert alphanumeric to integer

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 error
Post the error.

>>The data I need to convert is always actually a number
It 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>) = 0

Be One with the Optimizer
TG
Go to Top of Page

johnmitch38
Starting Member

9 Posts

Posted - 2013-11-18 : 09:47:47
>>CAST or CONVERT throws up an error
Post the error.
>>The error says "Cannot convert 150.00 to a int"

>>The data I need to convert is always actually a number
It 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
Go to Top of Page

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 too

SELECT CAST(REPLACE(YourintegerField,CHAR(160),'') AS int)
FROM Table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 Optimizer
TG
Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 Optimizer
TG
Go to Top of Page

johnmitch38
Starting Member

9 Posts

Posted - 2013-11-20 : 05:40:25
Brilliant, that worked a treat.

Many thanks.

JM
Go to Top of Page
   

- Advertisement -