topic Re: How to Query case insensitive in Alfresco Solr search ? in Alfresco Forum

How to Query case insensitive in Alfresco Solr search ?

devkinandanchau — Thu, 18 Oct 2018 12:49:48 GMT

Using Alfresco Community - 5.0.0 version.Below query using Solr returned result :1st Query : select cmisbjectId from TEST:doc where TEST:docType = 'Type1' But we want the case-insensitive search if there are docType value 'type1' || 'TYPE1' || 'tYpe1' || 'Type1' then it should give same number of

Re: How to Query case insensitive in Alfresco Solr search ?

angelborroy — Thu, 18 Oct 2018 13:26:11 GMT

Have you tried "CONTAINS" clause instead of "LOWER"?

Re: How to Query case insensitive in Alfresco Solr search ?

devkinandanchau — Thu, 18 Oct 2018 13:47:29 GMT

Wanna do exact string search but case-insensitive, contains will lead to wrong results.

Re: How to Query case insensitive in Alfresco Solr search ?

angelborroy — Thu, 18 Oct 2018 13:59:46 GMT

IMO first query is giving you all required results and it's case-insensitive, but probably I'm wrong.

Re: How to Query case insensitive in Alfresco Solr search ?

afaust — Thu, 18 Oct 2018 14:04:22 GMT

You cannot have case-insensitive exact matches - either you have an exact match, or a match ignoring case - there can by definition not be any overlap
AFAIK SOLR query does not support applying value transformation functions to field values - only to input values. SOLR does not store the original values and can only query a pre-built index, so there would be literally nothing to transform.
Of course executing with LOWER() against the database will be slower as the DB will have to apply that function to any value going through the filter condition. Depending on the query plan chosen by the DB, this can be many more rows than actually end up in the result.

If you want to query for case insensitive matches, your best bet is probably using FTS query language instead of CMIS. Though bear in mind that for FTS as well can by definition not achieve both at the same time (exact + insensitive match), but for different reasons. If you don't specify exact match per query modifier, you will get case insensitive matches, but due to tokenisation and stemming you may also get matches for slightly different values (i.e. "motor" vs. "motors").

Effectively, unless you start writing custom, special purpose SQL queries without using the standardised query languages, you will likely not be able to achieve 100% of what you want.

Re: How to Query case insensitive in Alfresco Solr search ?

jpotts — Mon, 22 Oct 2018 03:42:52 GMT

Cross-posted here: How to Query case insensitive in Alfresco Solr search? - Stack Overflow.

Old thread with basically the same question here: