varchar to 'array' for 'where in'

[wimkumpen]

Hello,

I have a varchar field where the values look like: "5,3,9"

I want to use those values in a 'where in':

select * from tabel where id in (varcharfield)

is something like this possible?

 

[r937]

it is possible, but horribly clumsy and inefficient

WHERE CONCAT(',',varcharfield,',') LIKE CONCAT('%,',id,',%')

your problem is the comma-delimited string of values stored in a single column, which violates first normal form

any chance you could redesign that?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[DonQuichote]

I think I would use the FIND_IN_SET function in such a case.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[wimkumpen]

I think both solutions can be used. But the FIND_IN_SET is a little bit cleaner to use. Thanks for your help.

 

[r937]

and both will not scale, i.e. the more rows you have, the slower it gets

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon