How to leverage JCR SQL2 Query with LIKE Operator

[mrudul]

Hi, 

We have a situation where we are currently querying to find an Image with the below pattern
abcd_108489356_en.jpg and it is giving the results.


SELECT * FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE(a,[/content/dam/some-company/creative-assets/automobiles/offers]) AND a.[jcr:path] LIKE '%/108489356_en.jpg' ORDER BY a.[jcr:created]

Sometimes images in the DAM would be something like 108489356_enfr.jpg

How can we leverage the above Query to find 108489356_enfr.jpg if 108489356_en.jpg doesn't exist? 

Thanks,

@

[Nitin_laad · Accepted solution]

@mrudul what about the below query?

SELECT * FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE(a,[/content/dam/some-company/creative-assets/automobiles/offers]) AND a.[jcr:path] LIKE 'abcd_108489356%' ORDER BY a.[jcr:created]

[mrudul]

Thanks, @Nitin_laad Nitin, This should work.

[mrudul]

@Nitin_laad @DEBAL_DAS 

Thanks for this.
Of late there is a small change in requirement. 
In DAM we would have something like this 

scenario 1: 1000590_en.jpg AND 1000590_fr.jpg
scenario 2: 1000590_enfr.jpg - (If this exist there will not be above, scenario 1)

And in the request, we always have either 1000590_en.jpg or 1000590_fr.jpg
How can we achieve this with a single Query, not successive Queries

[Nitin_laad]

@mrudulThis should be a two-step process, in my opinion. The first step is to retrieve the relevant data from JCR, and the second step is to apply JAVA logic to filter the record as per your requirement. 

[Jörg_Hoh]

That will work, but it will slow down the query quite a bit because it is no longer a substring operation, but you need are effectively doing a fulltext match on the path. Although given that the paths are not huge (in terms of text) it should be acceptable.

But be aware, that it might yield in unexpected results, because it will match basically everything containing that string. You need to post-process the results.

[DEBAL_DAS]

Another approach you could use also, like operator with NAME() function as shown below -

[mrudul]

Thanks @DEBAL_DAS Appreciate your help!