First of all, I have to call this out: this is a very poor data model. By storing data in CSV in a text field, you are sacrificing much of the innate capability of the Salesforce database or indeed any database to effectively relate, index, query, and search structured data. That's going to make all further implementation you do more expensive, less performant, and higher maintenance.
I'm also going to ignore for the purpose of discussion the constraint
The RoomInfo__c schema cannot change.
because in my opinion most of the viable solutions involve changing the schema.
As far as I can see, the single best decision you could make right now is...
Use a middleware platform and parse the data
Put some kind of middleware platform between your external source of truth and Salesforce. Parse that grimy CSV data and turn it into actual related records: each Property is a record, each Room is child record. (See below for discussion about modeling Nearby Activities and Features).
If you can't use a middleware platform, proposal #2 is...
Postprocess the data in Apex
CSV is not hard to parse, although fair warning, you will find a lot of quite bad CSV parsing code in Apex if you search. (Read all of RFC 4180 and be aware of specific quirks, if any, in how your source system interprets the CSV format).
You'd probably want a batch process or perhaps a Queueable that crawls inbound data in CSV format after it is split into RoomInfo records and parses it out into actual database fields.
Note that this will create a time gap where records enter your database in CSV format and then are picked up by the asynchronous job and converted to real data. You might be able to do it in a trigger depending on what your data volume looks like and how performant you can make your Apex. The details of the best solution will be dependent on how frequently the data is loaded or updated and at what volume.
Modeling Nearby Activities and Features
There are several different ways the Activities and Features elements could be modeled in real database objects and fields.
Relationships. Each Activity or Feature could be a record, and you could define junction objects connecting Room Info records to them. At the data volume you specify, this is likely to result in data skew, so this solution is probably contraindicated.
Multi-select picklists. Nobody really likes these; they're annoying to work with and report on. The advantage over #3, checkboxes, is that adding new Features or Activities would involve adding picklist entries but not adding a new field to the schema.
Checkboxes - one checkbox on the Room Info record per Activity or Feature that is supported. This can of course lead to a profusion of checkboxes, but it is easy to query and report on.
Structured data in text fields. JSON is much more common here than CSV. However, this solution is best for situations where you need to display complex data for one record in a UI, not where you need to perform complex searches or queries against it.
I would suggest that, assuming the set of Activity and Feature values is relatively small (and it sounds like it is, since you're presenting them in the UI via Checkbox Groups and are defining what is available via Custom Settings), modeling these elements as checkboxes is probably the right solution.
I'm not touching on the complexities of updating existing records at all - exercise for those who know more about the totality of this integrated architecture.
If you can't do either of those things, the only other option I see is...
Use SOSL
Unlike SOQL, SOSL can search long text fields (although it still can't filter them in a WHERE clause). You can dynamically construct a SOSL search for the features that you want to find in the long text field.
There's a lot of caveats and potential gotchas here.
- You need to understand how SOSL constructs a result set. Your searches are not guaranteed to return all potentially responsive records.
- If a property name overlaps with one of the features, your search for that feature will pick up all the rooms at that property (!)
- If feature names are shared across fields, you can't distinguish between matches in different locations.
- Your search query must be less than 4,000 characters long or you'll lose the ability to use filter logic.
- You'll never be able to return more than 2,000 results.
- The performance may or may not meet your expectations - worth experimenting. I don't have a lot of experience benchmarking SOQL vs. SOSL.
The Exact Match = false Case
All of this preceding is around the Exact Match = true case. The Exact Match = false case is likely to require you to write additional code depending on what data model solution you adopt, potentially running multiple queries with partial sets of your parameters and writing Apex or more likely JavaScript code to merge those result sets.
