Query ignoring case

[naresh]

I am trying to query an Alfresco repository for a document name ignoring case. What I have right now is this but it does not pick up documents that have "Debt" in their name (with upper case "D"):

    SELECT cmisbjectId FROM cmis:document WHERE cmis:name LIKE '%debt%'"

I understand that it is possible to do case insensitive queries using Alfresco extensions. How do I do that?

Thanks.
Naresh

[fmui]

Hi Naresh,

Try this:
SELECT * FROM cmis:document WHERE CONTAINS('~cmis:name:\'*debt*\'')


- Florian

[naresh]

Hi Florian,

That's giving me a 500 error from the Alfresco server:

org.alfresco.cmis.CMISQueryException - 09010944 line 1:56 no viable alternative at input '*' (decision=28 state 52) decision=<<569:1: predicate : ( comparisonPredicate | inPredicate | likePredicate | nullPredicate | quantifiedComparisonPredicate | quantifiedInPredicate | textSearchPredicate | folderPredicate );>> in where [query, whereClause, searchOrCondition, searchAndCondition, searchNotCondition, searchTest, predicate]

I captured the HTTP request going to the server - it is shown below:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<query
      xmlns="http://docs.oasis-open.org/ns/cmis/core/200908/"
      xmlns:ns2="http://docs.oasis-open.org/ns/cmis/messaging/200908/"
      xmlns:ns3="http://docs.oasis-open.org/ns/cmis/restatom/200908/">
    <statement>SELECT cmis:objectId FROM cmis:document WHERE CONTAINS('~cmis:name:'*Design*'')</statement>
    <searchAllVersions>false</searchAllVersions>
    <includeAllowableActions>true</includeAllowableActions>
    <includeRelationships>none</includeRelationships>
    <renditionFilter>cmis:none</renditionFilter>
    <maxItems>100</maxItems>
    <skipCount>0</skipCount>
</query>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I was guessing that the single quotes in the query were not escaped correctly, so I tried the following but that also gives a 500 error:

SELECT * FROM cmis:document WHERE CONTAINS('~cmis:name:\\\'*debt*\\\'')
‍‍‍

Please help.

Naresh