0

I would like to use the IN clause, but with the convert function. Basically, I have a table (A) with the column of type int. But in the other table (B) I Have values which are of type varchar. Essentially, what I am looking for something like this

select *
from B
where myB_Column IN (select myA_Columng from A)

However, I am not sure if the int from table A, would map / convert / evaluate properly for the varchar in B.

I am using SQL Server 2008.

Lamak
  • 69,480
  • 12
  • 108
  • 116
Dima R.
  • 995
  • 2
  • 14
  • 25
  • See http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html. You'll need to do a conversion in your subquery. – Kermit Aug 22 '12 at 15:38
  • 1
    You will get an implicit cast from varchar to int that will fail if the string column has any data that does not convert. Why are you storing ints as strings? – Martin Smith Aug 22 '12 at 15:41
  • 1
    @Dima seeing in your previous question, are all question were not answered even once? – John Woo Aug 22 '12 at 15:41
  • 1
    @njk . . . Or a conversion on myB_column outside the subquery. – Gordon Linoff Aug 22 '12 at 15:42
  • 1
    That means you should accept those answers that best addresses your needs. This will encourage the community members to help you more to sort out your problems. – SMC Aug 22 '12 at 17:09
  • @dima-r is there a good reason you did not accept any answer? – surfmuggle Aug 02 '13 at 20:18

5 Answers5

2

You can use CASE statement in where clause like this and CAST only if its Integer. else 0 or NULL depending on your requirements.

SELECT * 
FROM   B 
WHERE  CASE ISNUMERIC(myB_Column) WHEN 1 THEN CAST(myB_Column AS INT) ELSE 0 END
 IN (SELECT myA_Columng FROM A)

ISNUMERIC will be 1 (true) for Decimal values as-well so ideally you should implement your own IsInteger UDF .To do that look at this question

T-sql - determine if value is integer

Community
  • 1
  • 1
ClearLogic
  • 3,616
  • 1
  • 23
  • 31
0

Option #1

Select * from B where myB_Column IN 
(
      Select Cast(myA_Columng As Int) from A Where ISNUMERIC(myA_Columng) = 1
)

Option #2

Select B.* from B 
Inner Join
(
    Select Cast(myA_Columng As Int) As myA_Columng from A 
    Where ISNUMERIC(myA_Columng) = 1
) T
On T.myA_Columng = B.myB_Column

Option #3

Select B.* from B 
Left Join
(
    Select Cast(myA_Columng As Int) As myA_Columng from A 
    Where ISNUMERIC(myA_Columng) = 1
) T
On T.myA_Columng = B.myB_Column

I will opt third one. Reason is below mentioned.

Disadvantages of IN Predicate

Suppose I have two list objects.

List 1      List 2
  1           12
  2            7
  3            8
  4           98
  5            9
  6           10
  7            6

Using Contains, it will search for each List-1 item in List-2 that means iteration will happen 49 times !!!

SMC
  • 237
  • 1
  • 5
  • 29
0

You can also use exists caluse, 

select *
from B
where EXISTS (select 1 from A WHERE CAST(myA_Column AS VARCHAR) = myB_Column)
rs.
  • 26,707
  • 12
  • 68
  • 90
0

You can use below query :

select B.*
from  B
inner join (Select distinct MyA_Columng from A) AS X ON B.MyB_Column = CAST(x.MyA_Columng as NVARCHAR(50))
mehdi lotfi
  • 11,194
  • 18
  • 82
  • 128
-1

Try it by using CAST()

SELECT * 
FROM   B 
WHERE  CAST(myB_Column AS INT(11)) IN (
                                       SELECT myA_Columng
                                       FROM A
                                      )
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • looking at the question myB_Column is the column that needs casting. – ClearLogic Aug 22 '12 at 17:01
  • Thanks. I did this, but the other way around SELECT * FROM B WHERE CAST(myB_Column,AS INT(11)) IN (SELECT myA_Column FROM A) and it worked – Dima R. Aug 22 '12 at 17:05