Search code examples
postgresqlsql-like

LIKE clause with \ character in PostgreSQL

I have this behavior in PostgreSQL 9.3:

-- (1) this "doesn't" work
select 't\om' like '%t\om%'
-- result = false

-- (2) this works
select 't/om' like '%t/om%'
-- result = true

Why is the (1) query result false? What is the best way to get true in (1) query?

Solution

  • The \ has no special meaning in SQL except inside the condition for the LIKE operator where it can be used to escape the wildcard characters.

    But you can define a different escape character for LIKE which then makes the \ a "normal" character:

    select 't\om' like '%t\om%' escape '#';

    edit

    As Sunrelax has commented, you can also use an empty string as the "escape" sequence:

    select 't\om' like '%t\om%' escape '';