[SalesForce] How to perform Fuzzy search in a string field

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
enter image description here

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.

enter image description here

Best Answer

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

Related Topic