Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

SQl2 query with contains does not find any nodes

Magicr
Level 3
Level 3

Hello,

I'm facing following strange situation and I have no idea about this behavior, using AEM6.4. Given is following situation:

 

In my project exists some nodes they have in property 'sling:resourceType' the value "c109_video". So I created two queries to find those nodes.

Query one: 

SELECT * FROM [nt:base] AS n WHERE ISDESCENDANTNODE(n, [/content/sites]) AND n.[sling:resourceType] like 'c109_video' ORDER BY n.[jcr:created] ASC

The result list contains more than zero hits. 

 

Query two: 

SELECT * FROM [nt:base] AS n WHERE ISDESCENDANTNODE(n, [/content/sites]) AND CONTAINS(n.[sling:resourceType], 'c109_video') ORDER BY n.[jcr:created] ASC

 The result list is empty.

 

Which reasons are responsible for this difference?

1 Accepted Solution
Magicr
Correct answer by
Level 3
Level 3

Some associates there works also with AEM faced the same situation. They have also no explainnations for this strange behavior. They think there are not documented changes in Service Pack 6, so thier recommendation is using like operator instead of contains

View solution in original post

0 Replies
aemmarc
Employee
Employee

CONTAINS and LIKE do two different things.

 

Magicr
Level 3
Level 3

Ok und what is difference? What is the usecase of contains?

Magicr
Level 3
Level 3

I do not understand the difference for my case. What I know, if you use contains it looks for exact matching. So you can define more than one pattern to find occurences. While with like is allowed to use wildcards (the % sign). My provided example has an exact match, or not? 

Magicr
Level 3
Level 3
If I look into jcr:title I will get a result with contains, other node properties do not work. Any ideas?
Magicr
Level 3
Level 3

Based on https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/search-inside-a-page-conte... is possible that contains looks only in nodes from type 'cq:Page' and not in other node types? That wold explain this strange situation.

abhilakhpatre1
Level 2
Level 2

Your first query looks fine and gives a expected results. If you want to search with like clause you can also introduce wildcard characters based upon your requirements to match the words (trailing or starting)

 

When it comes to CONTAINS , it will also work for non-delimetered words (simplewords) for it to be worked for delimited words like in your case _ underscore, you have to add double quotes to the text you are searching. I have rephrased your query with double quotes like below :

 

SELECT * FROM [nt:base] AS n WHERE ISDESCENDANTNODE(n, [/content/sites]) AND CONTAINS(n.[sling:resourceType], “'c109_video'”) ORDER BY n.[jcr:created] ASC

 

For more details you can also refere below links to understand the difference:

https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/difference-between-contain...

 

https://stackoverflow.com/questions/48077571/why-does-contains-find-inequal-text-strings-in-jcr-sql2

Hope this might be helpful for you 🙂

Magicr
Level 3
Level 3

Thanks for trying to help. I tried your suggested query and the result list is still empty. 

Update: It seems only the property jcr:title returns a non empty resultlist. What  a hell?

Jineet_Vora
Level 4
Level 4

@Magicr,

Difference between Contains and Like:
1. Contains: Searches the given string anywhere in the data

2. Like: Works with regular expression and searches for an expression. e.g. %adobe%

Magicr
Level 3
Level 3
@Jineet_Vora I think you haven't understood my question right. And I haven't marked this as solution (whoever it was).
Magicr
Correct answer by
Level 3
Level 3

Some associates there works also with AEM faced the same situation. They have also no explainnations for this strange behavior. They think there are not documented changes in Service Pack 6, so thier recommendation is using like operator instead of contains

View solution in original post