Find a certain number inside a mysql string

Question

Good morning, everyone. I’m having a question I can’t seem to solve.

In a given table of a BD I have a column that saves a string as follows:

1,2,3,4,5 Line 1
11,22,33,44,55 Line 2

Now I need to do a search to select only the lines that have given number by ex:

Select column X where it contains the number 2

I tried to do the search using the code below, but it is returning me both line 1 and line 2, but I only needed line 1 which is where there is exactly number 2 and not the line where there is number 22

$sql = 'SELECT `ativos` from `produtos` WHERE `ativos` LIKE "%'.$id.'%"';

Does anyone have any idea how I can solve this problem? Thanks in advance

Answer 1

Use the section below. It will consider commas as separators.

$sql = 'SELECT `ativos` 
from `produtos`
 /*Separadores no meio da sequência */
WHERE `ativos` LIKE '%," . $id . ",%' 
 /*Separadores no início da sequência */ 
OR `ativos` LIKE '" . $id . ",%'  
 /*Separadores no fim da sequência */
OR `ativos` LIKE '%," . $id . "'  

There must be some other technique that solves your problem in a better way, but this one I believe already does what you want.

Answer 2

Try as follows using the FIND_IN_SET

Supposing your field assets be composed of relationship id’s I took the liberty of using an adapted sql where you get a id. Anything alerts in the comments whether it suits you or not.

$sql += "SELECT ativos from produtos WHERE FIND_IN_SET(" + $id + ",ativos)"

Answer 3

Good morning, from what I understand you need to display only the first line of your search, so change your sql code to:

$sql = 'SELECT `ativos` from `produtos` WHERE `ativos` LIKE "%'.$id.'%" LIMIT '1' DESC';

Note: DESC list by the latest record and the oldest ASC.