Escape special characters in JCR SQL2 Query

Question

I am using a JCR SQL2 query as below:

SELECT parent.* FROM [rep:User] AS parent INNER JOIN [nt:unstructured] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent,'/website/test/users') AND (UPPER(child.[email]) ='[email protected]') AND (child.[email] IS NOT NULL)

I am getting the correct reqults in this case.

But how can I escape apostraphe in email in JCR SQL2 query? For example if i search firstname_last'[email protected], I am getting java.text.ParseException. How can i escape special characters in JCR SQL2 query?

Answer 1

You can potentially use the single quote / apostrophe ' as an escape character. However, I am doubtful if your query would ever return results because you are comparing Upper case values in the repo to lower case values in your query.

Added the query below without converting to upper case and escaping the apostrophe.

SELECT parent.* FROM [rep:User] AS parent INNER JOIN [nt:unstructured] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent,'/website/test/users') AND (child.[email] ='firstname_last''[email protected]') AND (child.[email] IS NOT NULL)