Coldfusion Solr Search - equivalent to LIKE %string% in SQL

Question

How to do a SOLR search with the same logic of the following SQL search query?

SELECT * FROM user where email LIKE '%ben%'" 

I tried the following,

<cfscript>
    mysearch = new com.adobe.coldfusion.search();
    searchResult = mysearch.search(collection="mycollection", criteria='ben*'); 
</cfscript>

1. criteria='ben*' matched 'raw_ben@yahoo.com' and 'ben@yahoo.com' but didn't return string 'roben roben'.

2. criteria='ben~' matched 'raw_ben@yahoo.com' and 'ben@yahoo.com' but didn't return string 'roben roben'.

Same goes for all fuzzy search, wild search attempts.

Answer 1

you need to change the fieldType for your field which can generate tokens using solr.EdgeNGramFilterFactory With the help of which you can generate the tokes e.g. abhijit would generate abh, abhi, abhij, abhiji, abhijit and hence would match all these combination for your query.

with the second EdgeNGramFilterFactory it will generate the tokens jit, ijit, hijit, bhijit, abhijit, iji, hiji, bhiji etc. so on...

try with below field type

<fieldType name="text_reference" class="solr.TextField" sortMissingLast="true" omitNorms="true" positionIncrementGap="100">
   <analyzer type="index">
     <tokenizer class="solr.KeywordTokenizerFactory"/>
       <filter class="solr.LowerCaseFilterFactory"/>
       <filter class="solr.EdgeNGramFilterFactory" minGramSize="3" maxGramSize="50" side="front"/>
       <filter class="solr.EdgeNGramFilterFactory" minGramSize="3" maxGramSize="50" side="back"/>
   </analyzer>
   <analyzer type="query">
      <tokenizer class="solr.KeywordTokenizerFactory"/>
         <filter class="solr.LowerCaseFilterFactory"/>
   </analyzer>
</fieldType>