SQL2 Query in CRX to search assets which has non alphanumeric charcters | Community
Skip to main content
C
cxasha
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
AMANATH_ULLAHCommunity 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
    C
    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
        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

          Terms & ConditionsAccessibility statement

          Loading footer...