Check if value is in a column

Asked

Viewed 145 times

-2

I need to check if a value is in a column in the comic book. My column is separated by commas, thus: 1,2,3,4,5. So I would like to know how to check if the value 2, for example, is in any row in the SQL column.

Select All from my_table when 1 is in my_column

Table structure

inserir a descrição da imagem aqui:

php sql

  • "I need to check if a value is in a column in the BD". You can edit the question and show an example of columns with rows? ;)

  • Ready. See if it’s more visible.

  • You want to know the php way to do this operation or in the same bank?

  • 1

    No sample of what you tried? Then your question can be closed as it is not very clear, nor do we know if what you want (where the problem is) is in sql, php, or a mix of the two...

  • 3

    I strongly suggest reading: How to create a [MCVE]

  • Okay, try to understand now.

  • Yes, and if the column is: 102,201,304,852,214. The line will be selected and there is no 2 only in it.

  • And if 2 is at the end of the string: 104,201,450,201,2 ?

  • Why did you delete the answers? kkj.

  • Because if they didn’t solve your question, they only generated what we call visual pollution =], better delete. As I will do with that comment in a moment.

Show 5 more comments

1 answer

0


Actually your table was incorrectly designed. The correct one would be to have a support table with the codes and a link to this table. But if there is no possibility to change the table you can use the following options:

1) DISTINCT + SUBSTRING_INDEX + Support Table

Applying the theory of that answer we have the following query for the proposed search:

SELECT *
  FROM (SELECT DISTINCT t.id,
                        t.id_busca,
                        t.players,
                        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.players, ',', n.numero), ',', -1)) AS ocorrencia
          FROM tabela t
               CROSS JOIN numeros n
         ORDER BY t.id, n.numero) x
 WHERE x.ocorrencia = 2

You can check the execution on SQL Fiddle.

In the above solution we create a support table (called numeros) with possible positions. Note that the numbers are not the values and yes to the positions and with the CROSS JOIN with this table we perform the SUBSTRING_INDEX, thus allowing all positions (separated by a comma) to be covered. Note that I used only 10 numbers in the example, but if there are more possible positions the support table must be filled according to the possibilities (always for more).

1) FIND_IN_SET

Another solution is to use the function FIND_IN_SET:

SELECT t.id,
       t.id_busca,
       t.players
  FROM tabela t
 WHERE FIND_IN_SET(2, t.players)

See working on SQL Fiddle.

References:

  • 1

    First of all, thank you for the answer. And can you tell me, what’s the problem with storing information in a column like this? Because I have another table with the same type of structure, and it worked perfectly. Would the problem be at the time of processing this data?

  • 1

    @Vitorleite makes the search difficult and there is no need to do such a heavy treatment to search and insert in this table. As was quoted in the other question you asked, there are other easier and more normalized ways to solve this problem

Browser other questions tagged php sql

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