SQL2 Query in CRX to search assets which has non alphanumeric charcters

4 replies
878 views
Translate

Hello,

Could you help me to get the CRXD | SQL2 Query to search assets which has non alphanumeric characters

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

AMANATH_ULLAHAccepted solution

New Participant

@cxasha

One possible reason for this error could be the presence of a large number of assets in the /content/dam folder.

Please try running this on a DAM folder with fewer assets.

If the issue persists, check your error logs and share them here.

Amanath Ullah

MukeshYadav_

New Participant

Hi @cxasha ,

Regex doesn't works as expected with SQL2.

So try groovy or we may mention special(non alpha numeric ) character one by one explicitly like below(will fetch all the asset with jcr:title having atleast one of any mentioned characters in or condition)

Based on requirement other property can also added like dc:title or whatever required

SELECT * 
FROM [nt:unstructured] AS s 
WHERE ISDESCENDANTNODE(s, '/content/dam/we-retail/en/activities/running') 
AND s.[jcr:title] IS NOT NULL 
AND ( s.[jcr:title] LIKE '%/%' OR 
     s.[jcr:title] LIKE '%-%' OR 
     s.[jcr:title] LIKE '%\\%' OR 
     s.[jcr:title] LIKE '%!%' OR 
     s.[jcr:title] LIKE '%@%' OR 
     s.[jcr:title] LIKE '%#%' OR 
     s.[jcr:title] LIKE '%$%' OR 
     s.[jcr:title] LIKE '%\_%' ESCAPE '\')

If spaces is also treated as non alpha it can be also added to condtion like below

s.[jcr:title] LIKE '%/%' OR

Thanks

Translate