I have this table in the database:
Tabla de Ejemplo:
id - registro - valor
1 - compras - 1,2,6
2 - ventas - 3,4
3 - deudas - 5
I want to make a query where I send only the number 3 and bring me the record 2 in this example.
Try with IN and do not, try it with CONTAINS and do not either.
What should I do?
There are several alternatives:
The find_in_set function returns the index in which a string is within a lista de strings (separated by a comma).
select * from tabla where find_in_set('3',valor) > 0;
Adding a comma before and after the field, to ensure that all values are surrounded by commas (including the first and last). That way, we can use like
select * from tabla where concat( ',', valor, ',') like '%,3,%';
Using regular expressions and the operator regexp
select * from tabla where valor regexp '(,|^)3(,|$)';
I recommend, under any point of view, not to concatenate different values within the same field. Instead, use a structure like the following:
╔════╦══════════╦═══════╗ ║ id ║ registro ║ valor ║ ╠════╬══════════╬═══════╣ ║ 1 ║ compras ║ 1 ║ ╠════╬══════════╬═══════╣ ║ 1 ║ compras ║ 2 ║ ╠════╬══════════╬═══════╣ ║ 1 ║ compras ║ 6 ║ ╠════╬══════════╬═══════╣ ║ 2 ║ ventas ║ 3 ║ ╠════╬══════════╬═══════╣ ║ 2 ║ ventas ║ 4 ║ ╠════╬══════════╬═══════╣ ║ 3 ║ deudas ║ 5 ║ ╚════╩══════════╩═══════╝
This structure will facilitate any operation you want to do, and it is the way in which a relational table and the database engine was designed to be used. In this way, logically it is much simpler and faster:
select * from tabla where valor = 3;
And going a step further, we could normalize the base to not have so many duplicate values occupying space for others. It would be a structure like the following:
Tabla: principal Tabla: nombresRegistros ╔════╦══════════╦═══════╗ ╔══════════╦═════════════════╗ ║ id ║ registro ║ valor ║ ║ registro ║ registro_nombre ║ ╠════╬══════════╬═══════╣ ╠══════════╬═════════════════╣ ║ 1 ║ 1 ║ 1 ║ ║ 1 ║ compras ║ ╠════╬══════════╬═══════╣ ╠══════════╬═════════════════╣ ║ 1 ║ 1 ║ 2 ║ ║ 2 ║ ventas ║ ╠════╬══════════╬═══════╣ ╠══════════╬═════════════════╣ ║ 1 ║ 1 ║ 6 ║ ║ 3 ║ deudas ║ ╠════╬══════════╬═══════╣ ╚══════════╩═════════════════╝ ║ 2 ║ 2 ║ 3 ║ ╠════╬══════════╬═══════╣ ║ 2 ║ 2 ║ 4 ║ ╠════╬══════════╬═══════╣ ║ 3 ║ 3 ║ 5 ║ ╚════╩══════════╩═══════╝
Even, if you wanted to obtain the format with which you had been working from this structure, it would be obtained with the following query:
select id, registro_nombre, group_concat(principal.valor) as valor
from principal
left join nombresRegistros
on principal.registro = nombresRegistros.registro
group by id;
This is the way you can best manage a database.
If a string is what you keep in the value column, you could use the LIKE of sql clause
SELECT * FROM tu_tabla WHERE valor LIKE '%3%';
What will return the records that contain a 3 in the value column.