0

I have the below column in table x where SharedBagsIds contains the string (1,3,4)

I am trying to write an SQL query that contains the in clause as follow

select * from .... where id in (x.SharedBagsIds)

but this line id in (x.SharedBagsIds) is generating an error Conversion failed when converting the varchar value '1,3,4' to data type int.

is there a way to fix this issue?

enter image description here

Sora
  • 2,465
  • 18
  • 73
  • 146
  • 1,3,4 is not an integer. You need to first separate them to their individual values . Is it 134 or 1 and 3 and 4 . – VTi Jun 19 '20 at 10:56
  • id is int, so it tries to convert the other operand to that type. Try `select * from .... where CONVERT(id AS VARCHAR) in (x.SharedBagsIds)` – Madushan Jun 19 '20 at 10:57

2 Answers2

0

This is not how the IN clause is used and you're comparing an integer (your id) to a string ('1,3,4'). You'll need to split the column value into multiple values, then check if the id matches any of the values:

WHERE (',' + RTRIM(SharedBagsIds) + ',') LIKE '%,' + @id + ',%'

See this answer.

Or consider extracting SharedBagsIds into its own table, storing comma-delimited values in a database field is not ideal.

Felipe Zavan
  • 1,654
  • 1
  • 14
  • 33
0

Your table is not in 1st normal form which states that every cell should be atomic. 'SharedBagsIds' contains more than one value which should always be avoided. Read about many to many relationships in SQL and modify the table accordingly.

Suppose I have table Teacher and another table student, then to show the relationship between them, create another table which contains teacherid and studentid as the composite primary key. Thus you can show individual mapping without having the need to put multiple ids like 1,3,4 in single cell.

Hope this helps.

Rahul Devadiga
  • 1
  • 1