Escaping in a ILIKE query

Question

I need to do a query that search for a text with 'Nome % teste \ / ' as prefix. I'm doing the query using:

where "name" ILIKE 'Nome a% teste \ /%' ESCAPE 'a' (using a as escape character).

There is a row that match this, but this query returns nothing. Removing the slash ('Nome % teste \'), it works. But I don't see why the slash is a problem, since the default escape is a backslash and I've changed it to 'a' in this test.

There is something that I'm missing? (I've consulted TFM)

Answer 1

Use the "ESCAPE" specifier

WHERE "name" ILIKE 'Nome ~% teste \\/' ESCAPE '~' 

http://www.postgresql.org/docs/8.2/static/functions-matching.html

Note: you still need to have the \ twice for the string parser.

Without the ESCAPE you would need to do

WHERE "name" ILIKE 'Nome \% test \\\\/' 

( 4 \ 's to represent one literal \ )

---

Thanks, but I still have the original issue with the slash. Searching with

WHERE "name" ILIKE 'Nome \% test \\\\/%' 

don't give me a result, while

WHERE "name" ILIKE 'Nome \% test \\\\%' 

(removed the slash, that is present in the row) works as expected. – Kknd

its possible your string does not have a literal "/" like you specified. you possibly have a null, or other whitespace character inbetween. Or possibly, you have / in a different character set.

I would attempt to use this to test for that possible scenario

 WHERE "name" ILIKE 'Nome \% ' AND "name" ~* '\\.{1,10}/' 

which will return lines that have / separated by something( but not lines with no separation )

Answer 2

Have you tried just using the backslash to escape it like this:

where "name" ILIKE 'Nome \% teste \\\/';