Android SQLite LIKE escape wildcard

Question

On Android Jellybean 4.1 is it possible to escape a LIKE wildcard character and still have the like use the index?

Use:

where field like 'xyz\_abc'

as opposed to:

where field like 'xyz_abc'

Does escaping wildcards work on Android? And will it still use the index if the wildcard is escaped?

What I am currently doing is:

where field like 'xyz_abc' and lower(field) = lower('xyz_abc')

Which is horribly inefficient due to the wildcard character.

Thanks

Answer 1

You need to use the ESCAPE clause:

where field like 'xyz\_abc' escape '\'

See the section The LIKE and GLOB operators in the SQLite Documentation.