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
BedrockMission!

Learn More

View all

Sign in to view all badges

SQl2 query with contains does not find any nodes

Avatar

Avatar
Validate 1
Level 2
Magicr
Level 2

Likes

3 likes

Total Posts

33 posts

Correct Reply

2 solutions
Top badges earned
Validate 1
Ignite 3
Ignite 1
Boost 3
Boost 1
View profile

Avatar
Validate 1
Level 2
Magicr
Level 2

Likes

3 likes

Total Posts

33 posts

Correct Reply

2 solutions
Top badges earned
Validate 1
Ignite 3
Ignite 1
Boost 3
Boost 1
View profile
Magicr
Level 2

22-09-2020

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?

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Validate 1
Level 2
Magicr
Level 2

Likes

3 likes

Total Posts

33 posts

Correct Reply

2 solutions
Top badges earned
Validate 1
Ignite 3
Ignite 1
Boost 3
Boost 1
View profile

Avatar
Validate 1
Level 2
Magicr
Level 2

Likes

3 likes

Total Posts

33 posts

Correct Reply

2 solutions
Top badges earned
Validate 1
Ignite 3
Ignite 1
Boost 3
Boost 1
View profile
Magicr
Level 2

02-10-2020

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

Answers (3)

Answers (3)

Avatar

Avatar
Give Back
Level 4
Jineet_Vora
Level 4

Likes

42 likes

Total Posts

45 posts

Correct Reply

22 solutions
Top badges earned
Give Back
Ignite 1
Boost 5
Boost 3
Boost 25
View profile

Avatar
Give Back
Level 4
Jineet_Vora
Level 4

Likes

42 likes

Total Posts

45 posts

Correct Reply

22 solutions
Top badges earned
Give Back
Ignite 1
Boost 5
Boost 3
Boost 25
View profile
Jineet_Vora
Level 4

22-09-2020

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

Avatar

Avatar
Boost 5
Level 2
abhilakhpatre1
Level 2

Likes

8 likes

Total Posts

7 posts

Correct Reply

1 solution
Top badges earned
Boost 5
Boost 3
Boost 1
Affirm 1
View profile

Avatar
Boost 5
Level 2
abhilakhpatre1
Level 2

Likes

8 likes

Total Posts

7 posts

Correct Reply

1 solution
Top badges earned
Boost 5
Boost 3
Boost 1
Affirm 1
View profile
abhilakhpatre1
Level 2

22-09-2020

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 🙂

Avatar

Avatar
Ignite 1
Employee
aemmarc
Employee

Likes

183 likes

Total Posts

243 posts

Correct Reply

92 solutions
Top badges earned
Ignite 1
Give Back 50
Give Back 5
Give Back 3
Give Back 25
View profile

Avatar
Ignite 1
Employee
aemmarc
Employee

Likes

183 likes

Total Posts

243 posts

Correct Reply

92 solutions
Top badges earned
Ignite 1
Give Back 50
Give Back 5
Give Back 3
Give Back 25
View profile
aemmarc
Employee

22-09-2020

CONTAINS and LIKE do two different things.