Case sensitive search using CONTAINS in SQL2 | Community
Skip to main content
S
Shallu_Rohilla
New Participant
October 16, 2015
Solved

Case sensitive search using CONTAINS in SQL2

  • October 16, 2015
  • 6 replies
  • 5784 views

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.
Best answer by smacdonald2008

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

6 replies

smacdonald2008
smacdonald2008
New Participant
October 16, 2015

This StackOverflow thread addresses this:

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

kautuk_sahni
kautuk_sahni
Employee
October 16, 2015

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

Kautuk Sahni
S
Shallu_RohillaAuthor
New Participant
October 16, 2015

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

S
Shallu_RohillaAuthor
New Participant
October 16, 2015

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
smacdonald2008Accepted solution
New Participant
October 16, 2015

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

Lokesh_Shivalingaiah
Lokesh_Shivalingaiah
New Participant
October 16, 2015

Hi Shallu,

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

Terms & ConditionsAccessibility statement

Loading footer...