How do I use ESCAPE in SQLite?

Question

Trying this answer and not having any luck:

I am using the SQLite Database browser (built with 3.3.5 of the SQLite engine) to execute this query:

SELECT columnX FROM MyTable

WHERE columnX LIKE  '%\%16'  ESCAPE '\'  

In column XI have a row with the data: sampledata%167

I execute the statement and get no data returned but no error either?

http://www.sqlite.org/lang_expr.html

(SQLite with C API)

Answer 1

I think the problem is that you are missing a % from the end of the pattern.

'%\%16%'

Also backslashes often cause confusion in various programming languages and tools, especially if there is multiple levels of parsing involved before the query gets to the database. To simplify things you could try a different escape character instead:

WHERE columnX LIKE '%!%16%' ESCAPE '!'

Answer 2

Your sample data ends in %167, but your query only matches things which end in %16. You may need to change your query to have a trailing % or end with 167 as your data does, depending on your needs.

When I try this in SQLite, it works just fine:

sqlite> create table foo (bar text);
sqlite> insert into foo (bar) values ('sampledata%167');
sqlite> select * from foo where bar like '%\%16' escape '\';
sqlite> select * from foo where bar like '%\%167' escape '\';
sampledata%167

You may want to try experimenting with this in the SQLite shell, on a simple example table like I show, to see if your problem still exists there.