REPLACE in the IN clause - MySQL

Question

REPLACE in the IN clause - MySQL

Navigation

#1 by (4 votes)

0

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

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. Should return two instead of one.

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

Can you tell me what might be happening?

mysql replace

asked by anonymous 04.02.2017 / 00:32

1 answer

Webservice consumption only runs on debugging under the Visual Studio IDE PHP mail does not send the email

score 4 · Accepted Answer

It's basically syntax problem.

You said that if you do this "on hand" it works:

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

It works only if idcategoria is exactly the string 10, 11, 29, 30, 31 , after all IN expects a comma separated list of items.

The way you did, '10, 11, 29, 30, 31' is a single item (the fact that you have commas within the string has no relation to the list of items that IN expects).

These two examples are completely different from the above:

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 a 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' )

Manual:

link

Note: What might work, in your case, is to apply your reasoning to an extra language that is generating query , there it 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's happening outside the SQL layer. When SQL renders the string, the quotation marks would not even be there. If so, a% of% of their language would still be effective.