SQL2 Query for case-insensitive | Community
Skip to main content
9
99spanchal
Level 2
August 14, 2023
Solved

SQL2 Query for case-insensitive

  • August 14, 2023
  • 3 replies
  • 2005 views

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!

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Harwinder-singh

@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.

 

3 replies

Mahedi_Sabuj
Community Advisor
Mahedi_SabujCommunity Advisor
Community Advisor
August 15, 2023

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

 

 

Mahedi Sabuj
    9
    99spanchalAuthor
    Level 2
    August 15, 2023

    Hi @mahedi_sabuj,

     

    Thanks for your reply! 

    I have tried that too. That  didnt work either.

      Mahedi_Sabuj
      Community Advisor
      Mahedi_SabujCommunity Advisor
      Community Advisor
      August 15, 2023

      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.

       

      Mahedi Sabuj
        aanchal-sikka
        Community Advisor
        aanchal-sikkaCommunity Advisor
        Community Advisor
        August 15, 2023

        @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

        Aanchal Sikka
          Harwinder-singh
          Community Advisor
          Harwinder-singhCommunity AdvisorAccepted solution
          Community Advisor
          August 15, 2023

          @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.

           

            Terms & ConditionsAccessibility statement

            Loading footer...