How to convert varchar column values into int?

Question

I have a column toysid which is of varchar datatype in one table. Also another table having same the column toysid with int datatype (I know I can modify column for first table but I have to follow some procedure to keep it as varchar).

I have written following query to join the table:

select study.toysid 
from study 
join concepts on study.toysid = concepts.toysid

The query returns error:

Conversion failed wher converting varchar datatype into int

Hence I have tried below query:

select study.toysid 
from study 
join concepts on convert(int, study.toysid) = concepts.toysid

I got the same error. How to convert it? I have searched in many websites but I'm unable to get solution. Please anyone help on this.

Solution 1

You can omit such records which having non-numeric value and then cast with integer like below:

select Mystudy.toysid 
from concepts 
     join (Select study.toysid From study WHERe ISNUMERIC(study.toysid)= 1) As MyStudy 
          on convert(int, MyStudy.toysid) = concepts.toysid

Solution 2

Make sure you dont have anything other than numbers in your toysid column. I suspect there are characters other than numbers in that column. once you are sure, try the below query..

select study.toysid 
from study join concepts 
on cast(study.toysid as int) = concepts.toysid

or

select study.toysid 
from study join concepts 
on cast(study.toysid as varchar(100)) = cast(concepts.toysid as varchar(100))