MySQL Statement - Select where like Concat

Question

I have a field called "number" in my table modules. I want to show only specific entries, if the correct number is in my "number" field. Right now I am doing this with the following sql statement:

SELECT * 
from modules 
where number like CONCAT(
                    (SELECT id 
                    from account_metadata 
                    where hash='00cRTM')
                    ) 
ORDER BY date DESC

ID = 2385

This query is working fine as long as I have only one number insider the number field. As our system grows we need to save more than just one number insider our number field, therefore the entry could be something like this:

4235, 2385, 2058

More than one number, separated by commas.

Now my above sql query is not working anymore. Can someone help me and let me know how I should change my query to fetch all entries?

I would really appreciate any help.

Thanks in advance, Chris

Well then you probably want to be thinking about an `IN(1,2,3)` and not a LIKE and concat makes no sense, look it up in the manual — RiggsFolly, Jul 01 '21 at 12:33
@RiggsFolly IN combined with a subquery is not working. I already tried to replace like with IN but without any success — Christoph C., Jul 01 '21 at 12:35
A column called `number` that is defined as a `varchar` does anyone spot the logiic issue — RiggsFolly, Jul 01 '21 at 12:36
Does the column called `number` that is defined as a `varchar` contain a comma delimited list of numbers? — RiggsFolly, Jul 01 '21 at 12:37
@RiggsFolly please do not take care about any logic issues but if you can sleep better I renamed all columns and table names here on my question ;). So number isnt called number on live project ;) — Christoph C., Jul 01 '21 at 12:37
@RiggsFolly column number is a VARCHAR field and inputs come as an array comma separated — Christoph C., Jul 01 '21 at 12:38
[Suggested reading ___Is storing a delimited list in a database column really that bad?___](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — RiggsFolly, Jul 01 '21 at 12:41
@ChristophC. Check the below sql, something like this should work for you - "SELECT * FROM modules WHERE find_in_set((SELECT id from account_metadata where hash='00cRTM'), number) <> 0;" — skelwa, Jul 01 '21 at 12:45

score 0 · Answer 1 · answered Jul 01 '21 at 12:51

I found a solution by myself. It is working and all I need for testing purposes. Thanks to everyone who tried to help me.

SELECT * 
from modules 
where number like CONCAT('%',
                    (SELECT id 
                    from account_metadata 
                    where hash='00cRTM') ,'%'
                    ) 
ORDER BY date DESC

MySQL Statement - Select where like Concat

1 Answers1