How to perform Fuzzy search in a string field

Question

Problem Statement: We created a custom metadata which has a field called programs. Since we cannot create multi-select field with custom metadata we have created a string field and stored all values semi-colon separated like below

User would select programs in UI like Healthcare Coverage, Food Assistance Program and we need to only show questions related to the selected programs from the metadata we created

Includes and Excludes will only work with multi-select field and i looked at this solution from adrian SOQL: Bulk Fuzzy Searching and it doesnt help for my case as its doing a exact search. How should the SOQL be so that if user selects 'Food Assistance Program','Cash Assistance' it should go query the metadata record as shown in the screenshot.

Answer 1

You could actually do this in Apex using a set/list. Here's a barebones example:

Set<String> test = new Set<String>{'%search%', '%term%'};

SObject[] records = [SELECT Id FROM OBJECT_NAME__c WHERE NAME LIKE :test];

This will work in apex like my example above. This will NOT work in the developer console. For example

SELECT Id, Name FROM OBJECT_NAME__c WHERE Name LIKE ('%search%', '%term%') // Unknown error parsing query

Essentially the same as the other answer

Answer 2

I think you would need to use OR expressions in your where clause.

Select [...] from [...] where [insert built expression here]

and the built expression would be something like this:

Programs__c LIKE '%partial1%' OR Programs__c LIKE '%partial2%' [etc.]

I do not know how good the performance of this would be.