REPLACE in the IN - Mysql clause

Question

I have the following query below, and the idea is to take the value of the @subcategories variable and put in the query’s IN:

set @subcategorias = replace('10, 11, 29, 30, 31', '''', '');

SELECT 
car.NomeCaracteristica,
MAX(catcar.Valor) AS Valor,
catcar.Condicional AS Condicional
FROM 
caracteristica car
    inner join categoriacaracteristica catcar on car.IdCaracteristica =     catcar.IdCaracteristica
WHERE
catcar.IdCategoria IN (@subcategorias) AND
catcar.IdCaracteristica = 19 AND
catcar.Valor is not null 
GROUP BY
car.NomeCaracteristica,
catcar.Condicional;

Doing so does not return me the amount of records I need. It should return two instead of one.

I know this because if I put the value in the IN in hand (catcar.Idcategoria IN ('10, 11, 29, 30, 31')) it works.

Anybody can tell me what might be going on?

Answer 1

Basically it’s a syntax problem.

You said if you do it "in hand" it works:

catcar.IdCategoria IN ('10, 11, 29, 30, 31')

Works only if idcategoria be exactly the string 10, 11, 29, 30, 31, after all the IN expects a comma-separated list of items.

The way you did, '10, 11, 29, 30, 31' is an item only (the fact of having commas inside the string has nothing to do with the list of items IN waiting).

These two examples are completely different from the above line:

catcar.IdCategoria IN ( 10, 11, 29, 30, 31 )

and

catcar.IdCategoria IN ( '10', '11', '29', '30', '31' )

In this case, there are 5 different items, not one string as described in the question.

If you want to find data in a string separated by commas, the function is another:

WHERE FIND_IN_SET( catcar.IdCategoria, '10,11,29,30,31' )

Handbook:

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set

Note: what could work, in your case, is to apply your reasoning to an extra language that is generating the query, there is a completely different situation.

Example in PHP:

$lista = '10, 11, 29, 30, 31';
$sql = ' ... resto da query... WHERE catcar.IdCategoria IN ('.$lista.') AND ...';
mysqli_query( sql );

This already works because it is happening outside the SQL layer. When SQL processes the string, the quotes would not be there anymore. Then yes, a replace of the respective language would still be effective.