how to use replace function with in clause in mysql

Question

I want sql query which gives output as concat string

Sql Query

SELECT GROUP_CONCAT(nm) FROM xyz WHERE xyz.id IN (REPLACE(abc,"|",','))

where abc is string like 1|2|3|4 which is ids of xyz table above query only gives nm of 1st id in abc.I thing it creates query like

SELECT GROUP_CONCAT(nm) FROM xyz WHERE xyz.id IN ("1,2,3,4")

so (") may creates problem anyone can help.

possible duplicate of [SQL IN Clause In Stored Procedure](http://stackoverflow.com/questions/11354287/sql-in-clause-in-stored-procedure) — Chris Gessler, Jul 07 '12 at 08:08
@ChrisGessler - This is tagged MySQL, the question you linked in SQL Server. — MatBailie, Jul 07 '12 at 08:26
@Dems - still related to dynamic IN clauses and mentions several solutions like implementing a split function, etc... same techniques could easly be applied to MySql — Chris Gessler, Jul 07 '12 at 08:39

score 3 · Accepted Answer · answered Jul 07 '12 at 08:09

3

You can use LIKE, (but then its not going to use indexs)

SELECT GROUP_CONCAT(nm)
FROM xyz
WHERE CONCAT('|', abc, '|') LIKE CONCAT('%|', xyz.id, '|%');

answered Jul 07 '12 at 08:09

Puggan Se

Does MySQL have User Defined Table Valued Functions? So the OP can use `WHERE id IN ((SELECT split(@list, `,`))`? – MatBailie Jul 07 '12 at 08:24

Chris Gessler · Answer 2 · 2012-07-07T08:53:44.813

2

You can use INSTR like so:

SELECT GROUP_CONCAT(nm) 
FROM xyz 
WHERE INSTR(CONCAT('|', abc, '|'), CONCAT('|', xyz.id, '|')) > 0

Or you could implement a split function or use dynamic SQL.

edited Jul 07 '12 at 08:53

answered Jul 07 '12 at 08:47

Chris Gessler

is there a speed diference between instr and like? – Puggan Se Jul 07 '12 at 09:07
@AndriyM i know LIKE can do more then INSTR, but if you only need the functionality of INSTR, do i get a faster query with INSTR, or can i keep using LIKE as im used to – Puggan Se Jul 07 '12 at 09:40
@PugganSe: Yes, that's what I missed in your question and that's why I removed my comment. I hoped you wouldn't see it and I was wrong. :) Sorry about that piece of silliness of mine. – Andriy M Jul 07 '12 at 09:52

2 Answers2