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

[cxasha]

Hello,

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

[AMANATH_ULLAH]

@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]*'

[cxasha]

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

[AMANATH_ULLAH · Accepted solution]

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

[MukeshYadav_]

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