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

Asked

Viewed 446 times

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 containing comma separated values (CSV). How can I select lines containing any number from that set?

For example, select all rows in which the column numeros contain 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 everyone who has 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 well, but the line 4 is not selected.

Besides, I’ve been trying to avoid the LIKE, since this number will be typed by the user. And as always say, never trust the user input, even more so for a case like the LIKE.

mysql sql select select-sql csv

  • Numbers are always saved in an orderly fashion?

  • No, and number one was just an example, it would have to work for any other number

  • Yes, but the list of numbers would always be sorted?

  • No, since the server accepts any type of CSV that contains numbers then it would work with 4,1,3,5, for example

  • Ahh, so it’s more complicated :)

  • Yeah, I broke my head a lot with that right there haha

  • 1

    As mysql, the options are more restricted. Using like you would need to test all cases that are: Single element in the list, element starting the list, element in the middle of 2 elements of the list, and element ending the list. Did you think to do it this way? I don’t suggest as an answer because it wouldn’t be very elegant, but it would solve.

  • 1

    1 change the model , normalize this table 2 change the model , normalize this table 3 not being possible to apply an "unpivot" in this column , mysql has not ready that I know but there are contour solutions --- you should rethink a model when answers that should be simple become complicated.

  • What is the/the right model/normalisation/a to do this?

  • Putting in 3FN you would no longer have a multivariate column. This column would be a new table with a foreign key for the table that originated it. Then it would be easy to adjust the query using the 2 tables.

  • The only alternative would be to create another table? I’m thinking about the agility of the query. Imagine that I would have several users. These numbers would be like features they have. I wanted to query all users who have a certain resource. Wouldn’t it be too heavy when I join many users and many features? Not only heavy in order size, but heavy in disk space.

  • Try LIKE '%,@num,%' being @in a user input parameter.

  • Victor, in his case, ended up not taking any table row as specified above.

  • So try like '%,'||@num|',%' I tested it here and it looks like it worked.

  • This one didn’t work out with me no

  • Not changing the model implies a complex sql to do a simple thing

Show 11 more comments

1 answer

1


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

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

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 |
  • 1

    I would never have thought there would be such a thing. Thank you very much man!

Browser other questions tagged mysql sql select select-sql csv

You are not signed in. Login or sign up in order to post.