SQL2 Query in CRX to search assets which has non alphanumeric charcters | Community
Skip to main content
Level 2
August 20, 2024
Solved

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

  • August 20, 2024
  • 2 replies
  • 872 views

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.
Best answer by AMANATH_ULLAH

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

2 replies

AMANATH_ULLAH
Community Advisor
Community Advisor
August 21, 2024

@cxasha 

Use below query to search assets which have non alphanumeric characters

SELECT * FROM [dam:Asset] AS asset
WHERE ISDESCENDANTNODE(asset, '/content/dam')
AND NOT asset.[jcr:title] LIKE '[a-zA-Z0-9]*'

Amanath Ullah
cxashaAuthor
Level 2
August 21, 2024

Thank you! When I run this query. I am getting "Query execution resulted in an error!"

AMANATH_ULLAH
Community Advisor
AMANATH_ULLAHCommunity AdvisorAccepted solution
Community Advisor
August 21, 2024

@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_
Community Advisor
Community Advisor
August 21, 2024

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