Case sensitive search using CONTAINS in SQL2

[Shallu_Rohilla]

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

[smacdonald2008]

This StackOverflow thread addresses this:

http://stackoverflow.com/questions/20285579/querying-case-insensitive-paths-with-sql2-isdescendantno...

[kautuk_sahni]

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

I hope this would help you.

Thanks and Regards

Kautuk Sahni

[Shallu_Rohilla]

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

[Shallu_Rohilla]

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

[smacdonald2008 · Accepted solution]

This from the JCR spec:

"Two JCR names (N₁, L₁) and (N₂, L₂) are equal if and only if N₁ is equal to N₂ and L₁ is equal to L₂, 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

[Lokesh_Shivalingaiah]

Hi Shallu,

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