IN with CONCAT REPLACE MYSQL

Question

I have a table X which has y as column
x.y values are basically strings seperated by comma for example

X.y =   hello,world

now I have another table A which has columns id and b

id b  
1 hello  
2 world

Now, I am writing a query:

SELECT    
    (SELECT count(id) FROM A 
     where b IN (CONCAT("'",REPLACE(X.y,"'","','"),"'"))) as aCount  
FROM X

It is showing me zero whereas it should show 2 because
CONCAT("'",REPLACE(X.y,"'","','"),"'") could be 'hello','world'

You've got it backwards here. The `concat` function concatenates two strings __together__; it doesn't split them apart at a delimiter. Perhaps the answer to [this question](https://stackoverflow.com/questions/5928599/equivalent-of-explode-to-work-with-strings-in-mysql) can give you a little bit of direction. — RToyo, Jun 28 '18 at 21:06
I don't want to split, concat will basically add single quote at start and end of the string so if my string is **hello','world** it should become **'hello','world'** after concat — Rohit, Jun 28 '18 at 21:10
what is the relationship between the tables? is there a relationship? — Sebastian Brosch, Jun 28 '18 at 21:10
first table X is a summary of A so its column y basically contains the values A.b comma separated. — Rohit, Jun 28 '18 at 21:14
`IN` requires a literal list of separate elements, it doesn't work with a comma-delimited string. — Barmar, Jun 28 '18 at 21:16
@Rohit You're applying a clever idea, but it will not work because `concat` returns a __single string__, that is the equivalent of checking if column `b` is in `'hello','world'`. In order for your query to work, you'd need to have your query with the concatenated value as a single prepared statement, and then execute it. Here is [a different question](https://stackoverflow.com/questions/999200/is-it-possible-to-execute-a-string-in-mysql) that might give you insight into that. But I wouldn't consider that an good solution for what you're tryhing to solve. — RToyo, Jun 28 '18 at 21:16
@Barmer which is why i am using concat and replace to make it a literal list — Rohit, Jun 28 '18 at 21:17
`CONCAT` doesn't make a literal list, it makes one string. You need to use `FIND_IN_SET()`, not `IN()`. See the duplicate question. — Barmar, Jun 28 '18 at 21:18
It's the difference between `("'foo','bar'")` and `("foo", "bar")`. The first is a list of one string, the second is a list of two strings. You're creating the first. — Barmar, Jun 28 '18 at 21:18
@RToyo I think single string in () should work for IN can you provide solution to solve this? — Rohit, Jun 28 '18 at 21:21
You can try it out yourself to see that it returns a single concatenated value, and not multiple values: `SELECT CONCAT("'",REPLACE('hello,world',"'","','"),"'")`. As @Barmar suggested, you can use `FIND_IN_SET()` instead of `IN()` to do what you need to do. [The question](https://stackoverflow.com/questions/16208565/search-with-comma-separated-value-mysql) that appears in the yellow box at the top of your question will give you some insight into how to use it, and you should be able to get rolling from there. — RToyo, Jun 28 '18 at 21:26
@RToyo Yes I was looking that only, learned a totally new function today :D Thanx for your time (y) — Rohit, Jun 28 '18 at 21:31

IN with CONCAT REPLACE MYSQL

0 Answers0