Skip to main content
Shallu_Rohilla
Level 4
Oct 16, 2015 2:29 AM UTC
Solved

Case sensitive search using CONTAINS in SQL2

Hi,

I want to find all cq:PageContent nodes which has word like "STATUS" inside child nodes. I am using CONTAINS clause as the word "STATUS" is value of property of child node of cq:PageContent node. Using equals or LIKE return no result as this property is inside child node.. However my issue that I need to only search for upper case "STATUS" whereas CONTAINS bring me all results in both upper and lower case. Any idea as to how do I make CONTAINS search case-sensitive.

Sample query: SELECT s.* FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE(s, [/etc/content/test]) AND CONTAINS(s.*, 'STATUS') ORDER BY LOWER(s.titleSimplified)

Regards,

Shallu Rohilla

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
smacdonald2008
smacdonald2008Accepted solution
Level 10
Oct 16, 2015 2:29 AM UTC

This from the JCR spec:

"Two JCR names (N1, L1) and (N2, L2) are equal if and only if N1 is equal to N2 and L1 is equal to L2, according to the definition of string equality used in the String.compareTo method."

http://www.day.com/specs/jcr/2.0/3_Repository_Model.html

View in thread

6 replies

Lokesh_Shivalingaiah
Lokesh_Shivalingaiah
Level 10
Oct 16, 2015 2:29 AM UTC

Hi Shallu,

I think, 'contains' criteria doesnt have case sensitive but 'like' is case sensitive. See if you can use 'like' instead of 'contains'

Translate
smacdonald2008
smacdonald2008Accepted solution
Level 10
Oct 16, 2015 2:29 AM UTC

This from the JCR spec:

"Two JCR names (N1, L1) and (N2, L2) are equal if and only if N1 is equal to N2 and L1 is equal to L2, according to the definition of string equality used in the String.compareTo method."

http://www.day.com/specs/jcr/2.0/3_Repository_Model.html

Translate
Shallu_RohillaAuthor
Level 4
Oct 16, 2015 2:29 AM UTC

That means we cannot do case sensitive full text search i.e I cannot use this query and get only those nodes which has 'STATUS' only in upper case.:

SELECT s.* FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE(s, [/etc/content/test]) AND CONTAINS(s.*, 'STATUS') ORDER BY LOWER(s.titleSimplified).

 

Regards,

Shallu Rohilla

Translate
Shallu_RohillaAuthor
Level 4
Oct 16, 2015 2:29 AM UTC

Hi Kautuk,

The query you mentioned does equals search whereas my requirement is contains search since I want to search for string anywhere inside the jcr:content node.

Also I want case-sensitive contains search and not case insensitive.

Regards,

Shallu Rohilla

Translate
kautuk_sahni
kautuk_sahni
Level 9
Oct 16, 2015 2:29 AM UTC

Hi Shallu Rohilla

 

Please find below the query to get Upper case results.

String sqlStatement = "SELECT * FROM mix:title WHERE UPPER(jcr:title) = 'CASESENSITIVE'";  // UPPER keyword in the query would help you.

For more information please refer to :- https://docs.jboss.org/exojcr/1.12.13-GA/developer/en-US/html/ch-jcr-query-usecases.html#JCR.FindNodesCaseInsensitive

I hope this would help you.

 

Thanks and Regards

Kautuk Sahni

Translate
Kautuk Sahni
smacdonald2008
smacdonald2008
Level 10
Oct 16, 2015 2:29 AM UTC

This StackOverflow thread addresses this:

http://stackoverflow.com/questions/20285579/querying-case-insensitive-paths-with-sql2-isdescendantnode-in-cq5

Translate
Terms & ConditionsAccessibility statement

Loading footer...