aemjcrjcr-sql2

Creating a search query with wildcard in SQL2

I've got a request about a search, using wildcard.

I created the following query

SELECT * FROM [nt:base] AS p WHERE
ISDESCENDANTNODE(p, [/home/users/ldap/2]) AND
p.[sling:resourceType] = 'cq/security/components/profile' AND 
Contains (p.memberOf, 'SUG-pilot-anna')

and it works as expected. The task of this query is to look for users, who are member of a particular ldap group.

Because there is a lot of work to search for every single group, like 

SUG-pilot-anna
SUG-pilot-berta
SUG-pilot-ceta

, it would be easier and more efficient to use the wildcard. According serveral documantations, the LIKE operator is supported by SQL2.

My question is: how does looks the query with wildcard?

Thanks for your help/idears.

Solution

  • You can use the % wildcard and query for your requirements.

    SELECT * FROM [nt:base] AS p WHERE
ISDESCENDANTNODE(p, [/home/users/ldap/2]) AND
p.[sling:resourceType] = 'cq/security/components/profile' AND 
p.memberOf LIKE 'SUG-pilot-%'

    This would return the list of users who belong to any group name starting with SUG-pilot-. '%' matches zero or more characters and '_' matches exactly one character.

    The exact query which was working in my instance was

    SELECT * FROM [nt:base] AS p WHERE
ISDESCENDANTNODE(p, [/home/users/]) AND
p.[sling:resourceType] = 'cq/security/components/profile' AND 
p.memberOf LIKE '%de%'