Backslash works incorrectly in LIKE clause

Question

I'd to use LIKE and backslash to search some names. The problem is Postgres understands backslash as escape character in LIKE clause. I tried to turn on standard_conforming_strings but it doesn't help.

SELECT h.software_id
    ,h.software_name
FROM software h
WHERE software_name LIKE '%\%';

This query doesn't show anything whereas I have a software name '\'.

I've heard someone mentioned prepared statement. I use Hibernate to do the above query.

     <persistence-unit name="policyPersistence" transaction-type="JTA">
      <jta-data-source>java:jboss/datasources/MyApp</jta-data-source>
      <properties>
         <property name="hibernate.hbm2ddl.auto" value="validate" />
         <property name="hibernate.show_sql" value="false" />
         <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
         <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
      </properties>
   </persistence-unit>

JPA

query.setParameter(1, "%" + searchCriteria.getSoftwareName() + "%");

But Postgres doesn't escape backslash properly.

How to fix it?

Answer 1

Unlike in regular string literals, the backslash does indeed act as an escape character, changing standard_conforming_strings doesn't change this.

In order to be able to search for a % or a _ using LIKE you can the escape clause: where x like '%#_' escape '#' which defines the character # as the escape character for the like condition. Any character following the escape character will not be interpreted as a wildcard. So like '%#_' escape '#' searches for values that end with an underscore.

The backslash is the default escape character so not specifying the escape clause is the same as specifying escape '\' (this behavior is defined by the SQL standard).

So you need to use:

select *
from stuff.foo
where software_name like '%\%' escape '#'

to prevent Postgres from using \ as the escape character. The only other way is to escape the backslash using '%\\%' (note: only two backslashes here, not four as TimoSta suggested).

Here is the link to the Postgres manual:
http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-LIKE