SQL2 Query for case-insensitive

[99spanchal]

Hi,

I want to write a query in SQL2 to search through content fragments under /content/dam/testProject/content-fragments/en_us which is created using a specific template. In this query I also need to make sure that a property, myProperty contains a string "test". I have to make sure that the search is case-insensitive. I wrote the following query: 
SELECT * FROM [dam:Asset] AS asset WHERE ISDESCENDANTNODE(asset, '/content/dam/testProject/content-fragments/en_us') AND asset.[jcr:content/data/master/isFeatured] = true AND LOWER(asset.[jcr:content/data/master/myProperty]) = 'test' ORDER BY [jcr:score] DESC

This is not working as expected. I also tried the following:
SELECT * FROM [dam:Asset] AS asset WHERE ISDESCENDANTNODE(asset, '/content/dam/testProject/content-fragments/en_us') AND asset.[jcr:content/data/master/isFeatured] = true AND CONTAINS(asset.[jcr:content/data/master/myProperty], 'test') ORDER BY [jcr:score] DESC

This does not work either. 

Please note, that I am not getting any syntactical error. Just that I dont get the results as expected.

Could you please help me in writing a more targeted query for the requirement?

Thanks in advance!

[Mahedi_Sabuj]

You need to incorporate the LIKE property here

SELECT * FROM [dam:Asset] AS asset
WHERE ISDESCENDANTNODE(asset, '/content/dam/testProject/content-fragments/en_us') 
  AND asset.[jcr:content/data/master/isFeatured] = true
  AND LOWER(asset.[jcr:content/data/master/myProperty]) LIKE '%test%' 
ORDER BY [jcr:score] DESC

[99spanchal]

Hi @Mahedi_Sabuj,

Thanks for your reply! 

I have tried that too. That  didnt work either.

[Mahedi_Sabuj]

Can you please share jcr:content/data/master properties here?

Please verify that the below query returns the content fragment you are looking for. 

SELECT * FROM [dam:Asset] AS asset
WHERE ISDESCENDANTNODE(asset, '/content/dam/testProject/content-fragments/en_us') 

After obtaining the desired results, you can further evaluate by adding additional WHERE clauses. 

There might be other issues. I checked on crx/de and it was working fine.

[99spanchal]

this works, but when we are searching for %Test% it doesnt. I wanted a query that is case-insensitive. I read somewhere that CONTAINS() works better in such a case but it didnt work for me.

[Mahedi_Sabuj]

Hi @99spanchal, I'm not sure whether it's a typo, but you should search for "%test%" with a lowercase "t".

[aanchal-sikka]

@99spanchal 

Please refer to the blog https://www.albinsblog.com/2020/05/how-to-enable-case-insensitive-search-in-aem-lucene.html 

We need to enable the insensitive search in AEM with Lucene, for query to work.

Please verify the indexes as described in the blog

[Harwinder-singh · Accepted solution]

@99spanchal  You can use a combination of LOWER and UPPER function indexes to achieve this. 

SELECT * FROM [dam:Asset] AS asset WHERE ISDESCENDANTNODE(asset, '/content/dam/testProject/content-fragments/en_us') AND asset.[jcr:content/data/master/isFeatured] = true AND (LOWER(asset.[jcr:content/data/master/myProperty]) = "test" OR UPPER(asset.[jcr:content/data/master/myProperty]) = "TEST" ) ORDER BY [jcr:score] DESC.

I know this is far from ideal but with this you should be able to search nodes with property values like 'test', 'Test', 'TEst', 'TesT' , 'TEST'.

You always have an option to enable case-insensitive feature on lucene indexes. Refer to OAK documentation for more details.

Let me know if this works for you.