How to escape underscore in the string query in hibernate and SQL?

Question

The _ (underscore) given in the SQL query is not honored.

Example :

SELECT * FROM employee WHERE NAME LIKE '%k_p%';

This matches and brings many rows apart from rows which contain k_p

Could someone please assist on how to achieve this in SQL and also in Hibernate? Thanks.

You can specify an [ESCAPE character](http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#operator_like). For example LIKE '%k|_p%' ESCAPE '|' . — georgepsarakis, Jun 14 '13 at 16:20
Possible duplicate of [MySQL LIKE query with underscore](https://stackoverflow.com/questions/22167132/mysql-like-query-with-underscore) — Roham Rafii, Jan 21 '19 at 13:55

score 90 · Accepted Answer · answered Jun 14 '13 at 06:46

90

Have you tried escaping it:

SELECT * FROM employee WHERE NAME LIKE '%k\_p%';

\_ instead of just _.

answered Jun 14 '13 at 06:46

Michał Powaga

My string had several underscores that I wanted to treat literally, but I also wanted to match `%` somewhere in there, and didn't want to do lots of escaping. I found I can use `RLIKE` instead of `LIKE` and just use the Regex syntax `.*` instead of `%`, and input the underscores plainly. I might prefer this in SQL from now on since like JSON, I might prefer Regex as another common tool to use for any situation. – Pysis Apr 12 '18 at 20:51
is there an inbuilt function? – user1735921 Jan 21 '19 at 07:48
@Pysis `.*` is implicit. You can substitute `rlike 'k_p'` for `like '%k\_p%'`, and also `k_p$` for `%k_p`, and `^k_p` for `k_p%`. – Nuno André Dec 02 '19 at 04:43

score 0 · Answer 2 · edited Aug 20 '19 at 07:17

0

I know it is quite late but can be solution for other programmers. You can try

SELECT * FROM employee WHERE NAME LIKE '%k[_]p%';

edited Aug 20 '19 at 07:17

B--rian

answered Aug 20 '19 at 06:48

Joginder Malik

Welcome to SO, we appreciate your input! Please edit your question and add a short explanation of your code. – B--rian Aug 20 '19 at 07:06
This does not work in MySQL, at least in version 5.5.30. – ssurba Sep 05 '22 at 05:59

2 Answers2