In clause giving error while passing string values to integer column

Question

I have query like below

select * from table1 where id in (select ids from table2 where id = 100)

The subquery returns me output like 34,35,55,66 if i run it individually.

But my above query gives error as

Conversion failed when converting the varchar value '34,35,55,56' to data type int.

The id column in table1 is int. I am aware that i can do this in 2 seperate queries but if there is any way to do in single query then please let me know. Many thanks in advance

Please don't store multiple values in one column. That is a real pain like in your case. You should really change your DB structure. — juergen d
– juergen d, Commented Jun 24, 2013 at 13:05
Even an existing DB can be changed... Please think about doing it even if you have to change a few things. — juergen d
– juergen d, Commented Jun 24, 2013 at 13:08
There is a logical difference between a single string that happens to contain sets of digits and commas, and multiple integers. This is as true in SQL as it is in practically any other language I can think of. It really would be better to change the structure, because you'll continue to hit pain points with this current design. If you insist on continuing, you need to look up splitting strings on commas - which has been asked and answered plenty of times on here. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 24, 2013 at 13:09
@Damien_The_Unbeliever can you give me link where we can split string in commas — DevelopmentIsMyPassion
– DevelopmentIsMyPassion, Commented Jun 24, 2013 at 13:12

Peter Alfvin · Accepted Answer · 2013-06-24 13:57:27Z

1

I don't know about sql server and the comments about this kind of structure being undesirable notwithstanding, you can do it in mysql with the following, per this fiddle.

select distinct id from table1 join table2 on find_in_set(id,ids)

Update: I just realized this doesn't reflect table2 having it's own id field and restricting the search based on that. If you need help modifying the query to accomodate let me know and I'll update it and the fiddle.

Update 2: Here's a lame but perhaps valid attempt to do this in SQL Server with its much more limited string facilities, per this fiddle. If anyone knows of a simpler find_in_set equivalent for SQL Server, I'd be curious to know what it is.

select distinct id from table1 join table2 
  on patindex('%,'+cast(id as varchar)+',%',ids)>0 or
  patindex(cast(id as varchar)+',%',ids)>0 or
  patindex('%,'+cast(id as varchar),ids)>0 or
  patindex(cast(id as varchar),ids)>0

edited Jun 24, 2013 at 13:57

answered Jun 24, 2013 at 13:18

Peter Alfvin

29.6k9 gold badges72 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Peter Alfvin Over a year ago

Looks like you can use msdn.microsoft.com/en-us/library/ms188395.aspx in lieu of find_in_set

DevelopmentIsMyPassion Over a year ago

Is it possible for you to convert this to sql?

DevelopmentIsMyPassion Over a year ago

I give you +1 for your efforts. But just leave it now because i dont think its possible with single query. I give up :(

Peter Alfvin Over a year ago

@AshReva: The update I made to my answer 16 hours ago works in SQL Server per the fiddle link I included. Were you looking for some other version of SQL?

Collectives™ on Stack Overflow

In clause giving error while passing string values to integer column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related