Select a row where a column has an element of a CSV

Question

Select a row where a column has an element of a CSV

Navigation

#1 by (1 votes)

1

I have a table in the database with the following structure:

+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| numeros | text    | YES  |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+

In it, I have the following values:

+----+-------------------------------------+
| id | numeros                             |
+----+-------------------------------------+
|  1 | 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 |
|  2 | 1,3,5,9,11,13,15                    |
|  3 | 2,4,6,8,10,12                       |
|  4 | 1                                   |
|  5 | 15                                  |
+----+-------------------------------------+

numeros is a column that contains comma-separated values (CSV). How can I select rows that contain any number from this set?

For example, select all rows where the numeros column contains the value 1 .

I tried to make cases like:

mysql> SELECT * FROM 'tabela' WHERE 'numeros' LIKE '%1%';
+----+-------------------------------------+
| id | numeros                             |
+----+-------------------------------------+
|  1 | 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 |
|  2 | 1,3,5,9,11,13,15                    |
|  3 | 2,4,6,8,10,12                       |
|  4 | 1                                   |
|  5 | 15                                  |
+----+-------------------------------------+

In this case he ends up selecting all who have the number 1, literally. As is the case with lines 3 and 5.

mysql> SELECT * FROM 'tabela' WHERE 'numeros' LIKE '%1,%';
+----+-------------------------------------+
| id | numeros                             |
+----+-------------------------------------+
|  1 | 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 |
|  2 | 1,3,5,9,11,13,15                    |
+----+-------------------------------------+

Already in this, it seems to work fine, but line 4 is not selected.

Other than that, I've been trying to avoid LIKE , since that number will be entered by the user. And as they always say, never trust user input , yet more for a case such as LIKE .

mysql sql sql-select select csv

asked by anonymous 15.03.2017 / 18:35

1 answer

Use require or directly call the * .php file Data in listview (uwp)

score 1 · Accepted Answer

You can use the FIND_IN_SET () function to find the substring in the set, for example:

SELECT * FROM tabela WHERE FIND_IN_SET('3', numeros);

It will return all that contain element 3:

| id |                             numeros |
|----|-------------------------------------|
|  1 | 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 |
|  2 |                    1,3,5,9,11,13,15 |
|  6 |                               3,1,8 |