MYSQL SEARCH WHERE VALUE matches comma separated string

Question

My table Property_types has a field PROPERTY_TYPE which contains a single value such as Residential, Business or Office.

When running a query

select * from property_types where property type like '%Residential,office%'

Gets all properties, but returns nothing.

How should I rewrite the query to select only residential and office types instead of all property types?

I don't want to use the OR operator, i.e. WHERE property_type = 'office' OR property_type = 'residential' because there are many property types that have other complex query operators.

I tried FIND_IN_SET, SEARCH OPERATOR, but nothing worked. Any help would be greatly appreciated.

Answer 1

Try the following:

SELECT *
FROM property_types
WHERE `property type` LIKE '%Residential%'
   OR `property type` LIKE '%office%'

For further troubleshooting, please share the input table and expected output table with examples.