Shielded backslash and ILIKE

Question

PostgreSQL

Good afternoon!

select '%123\\456%'

expectedly returns the string %123\456%

However, the query

select '123456' ilike '%123\\456%'

unexpectedly gives true.

The context is as follows: we are looking for the document number, the number does not contain a slash, there is a slash in the request (escaped, as in the example). The document is located, although it does not seem to be. This is where both the pgAdmin client and Hibernate converge.

What could be the problem?

Answer 1

The problem is that '%123\\456%' doesn't actually have a backslash ;)
A backslash is already an escape character in strings, and to cram one into a string, you have to write it twice. But in 'like' it is also an escape character, so to match one backslash with a like, it must also be escaped. In total, to match one backslash, you need to write it four times. Those. something like this:
plesk=> select E'\\' like E'%\\%';
?column?
- f
(1 row)
plesk=> select E'\\' like E'%\\\\%';
?column?
- t
(1 row)
Well, or specify another escape character:
plesk=> select E'\\' like E'%\\%' escape '#';
?column?
- t
(1 row)
So if the document is not found with four slashes, then everything is in order :)
Here it is written about it, 9.7.1: www.postgresql.org/docs/8.0/static/functions-matching.html