LIKE clause with \ in PostgreSQL

Question

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 query result (1) false? What is the best way to get truth in (1) query?

+3

sql-like postgresql

sunrelax Apr 28 17 at 15:27

source to share

2 answers

\

is an escape sequence, so you need to escape it too:

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

There is also a configuration option that you can set. See Escaping backslashes in Postgresql

+1

msanford Apr 28 17 at 15:29

source to share

a_horse_with_no_name · Accepted Answer · 2017-04-28T15:36:06+0000

\

doesn't really matter in SQL other than inside a clause for a statement LIKE

, where it can be used to escape wildcard characters.

But you can define another escape character for LIKE, which then makes the character \

"normal":

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

change

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

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