-1

I'm trying to build a database for a project of mine, and the user will be able to enter multiple parameters. I then need to find all database entries, where no other parameters exist in that column.

I have one column, where all these parameters are listed, seperated by , (car, truck, 4wd). I now need to find all entries, where there are no other parameters than the ones in the search. It doesn't need to be all of them, but no others.

Example: Parameters: car, truck

Entries:

car, truck -> valid
car, 4wd, truck, -> invalid
car -> valid

I am trying to do this more or less while the user types, so I would love it if I then didn't have to find the database entries and then use my php to file through all the invalid entries, but could somehow do it directly in my query.

SophisticOtter
  • 3
  • 6
  • "**no other parameters than the ones in the search**" so why does the presence of `4wd` in the second row make that row valid? – Paul Maxwell Jun 29 '22 at 07:26
  • 1
    please read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad a normalised table would make the query very simple – nbk Jun 29 '22 at 07:32

1 Answers1

0

You could try something like this:

SELECT * FROM <table>
WHERE 
-- Check if remaining string has length 0. If longer, then there is another string in the column beside the three replaced ones
LENGTH(
    -- Remove commas
    REPLACE(
        -- Remove string '4wd'
        REPLACE(
            -- Remove string 'truck'
            REPLACE(
                -- Remove string 'car'
                REPLACE(<column>, 'car', ''), 
            'truck', ''), 
        '4wd', ''), 
    ',', '')
 ) = 0
schlonzo
  • 1,409
  • 13
  • 16