int and varchar joined

join an int and varchar column

but the column that is varchar has words sometimes
I do not want that joined

LVL 1
rgb192Asked:
Who is Participating?
 
Ephraim WangoyaCommented:

select *
from tableA
inner join TableB on ( (Cast(TableB.Varhcharfield as Integer) = TableA.IntegerField)
                                  and (ISNUMERIC(TableB.Varhcharfield) = 1))
0
 
SharathData EngineerCommented:
ewangoya, converting to int will fail if the asker has , or . in the data. check this example by converting to INT.
I prefer converting to MONEY in such cases.
declare @table1 table(col1 int)
declare @table2 table(col2 varchar(10))
insert @table1 values(10),(20)
insert @table2 values ('10'),('23.00'),('123,234'),('invalid')
select *
  from @table1 t1
  join @table2 t2 
    on t1.col1 = convert(money,t2.col2)
 where ISNUMERIC(t2.col2) = 1

Open in new window

0
 
Ephraim WangoyaCommented:

True, we acquire more knowledge each day.
0
 
rgb192Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.