[SOLVED] Escape special characters in JCR SQL2 Query

Escape special characters in JCR SQL2 Query

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]) ='firstname_lastname@testemail.com') 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'name@testemail.com, I am getting java.text.ParseException. How can i escape special characters in JCR SQL2 query?

Solution

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''name@testemail.com') AND (child.[email] IS NOT NULL)