Escaping special characters while querying – JPA

When we are querying with ‘Like’ using JPA queries with JPQL/Native SQL query, we may face issues while searching for special characters like ‘%’ or ‘_’. We have to try escaping special characters while querying the database using JPA.

We can use an escape character to escape the search input containing these reserved special characters. Also, we can escape the characters we want to escape by adding the escape character such as character ‘\’.

In this little article, we will learn how to escape special characters while searching with like query using JPA.

Escaping special characters in SQL – JPA

Consider an example, where we are searching the string with value: ‘name%123’ or ‘name_123’. Both of these % and _ characters can cause returning wrong results, as these characters are wildcard characters and we use them with like queries in SQL.

1. Using ESCAPE

To escape any character in SQL “like” queries, We can use ESCAPE along with the escape character. The below SQL query shows how to escape ‘%’ and ‘_’ characters in the search text.

/*Escaping % character.*/
SELECT * FROM STUDENT WHERE NAME LIKE 'name\%123' ESCAPE '\' 
/*Escaping _ character*/
SELECT * FROM STUDENT WHERE NAME LIKE 'name\_123' ESCAPE '\' 

Using Escape in JPA

We can make use of the same example and write a custom query in JPQL to escape the characters from the input text.

Before writing the JPQL query, we have to add an escape character to the beginning of the input text character, which needs to be escaped.

The code snippet shown below adds the escape character ‘\’ to the beginning of the character, which should be escaped.

String name = name.replace("_", "\\_");
name = name.replace("%", "\\%");

The custom JPQL query given below shows how to use the ESCAPE keyword.

@query("SELECT s FROM Student s WHERE s.name LIKE :name ESCAPE '\\'")
public List<Student> searchStudentsByName(String name)

2. Using []

If we are using the MS SQL database, we can use square brackets([]) to escape the wildcard characters. We can escape the wildcard characters(%, _, etc) if we mention them inside [] brackets.

Select * from employee where name like '%[_]';

With Java code, replace the search input wildcards with escape brackets as shown below.

name = name.replace("%", "[%]");

Conclusion

In this little article, we learned about escaping the special characters in SQL and JPA using the ESCAPE keyword. This is helpful when we are searching for database records containing special characters.