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 can use an escape character to escape the search input containing these reserved special characters. We have to 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

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

1. Using ESCAPE

To escape any character in SQL like queries, We can use ESCAPE along with escape character. 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. The wildcard characters(%, _, etc) are escaped if they have mentioned 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 how to escape special characters in SQL and JPQL using the ESCAPE keyword. This is helpful when we are searching for database records containing special characters.

Happy coding! 🙂

You may also be interested in