MySQL select with subquery having replace

Question

So I have a data with format like ;1;;2; and then I need to use this number in a query so I thought I'd convert it to 1,2 and use that in a IN condition. In my table, the result should return 2 rows but instead it is returning only 1 row.

My query is like this. The subquery return 1,2 with no problem but only 1 row is retrieve.

select * 
 from wt_lists 
where id IN ((select replace (replace(sendto, ';;',','),';','') 
               from wt_stats where statsid IN (1)))

But when I try it with this. It returns the correct result, which in my case is 2 rows.

select * 
  from wt_lists 
 where id IN (1,2)

What am I missing here?

Answer 1

You are replacing the string:

';1;;2;'

To:

'1,2'

So, you SQL query looks like:

select * from wt_lists where id IN ('1,2') from wt_stats where statsid IN (1)

To use IN clause you need select different values in different rows.

I found this store procedure that does exactly what you need.

http://kedar.nitty-witty.com/blog/mysql-stored-procedure-split-delimited-string-into-rows/

I have not tested, but it is the way.

Obs: Like David said in the comments above, parsing the data in your application is a better way to do this.

Answer 2

I was doing it within the PHP but I thought I could save a database trip. Thank you!

Answer 3

Comma delimited strings need to be explicitly defined in the query in order to be used in the IN clause - there's countless examples on SO where people need to use dynamic SQL to incorporate user submitted comma delimited strings.

That said, I have a solution using the FIND_IN_SET function:

SELECT DISTINCT wl.* 
  FROM WT_LISTS wl
  JOIN (SELECT REPLACE(REPLACE(ws.sendto, ';;',','),';','') AS ids
          FROM WT_STATS ws
         WHERE ws.statsid = 1) x ON FIND_IN_SET(wl.id, x.ids) > 0