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

http://stackoverflow.com/questions/9076561/android-sqlitedatabase-query-with-like take a look there it will help you probably! — Pavlos, Dec 11 '12 at 23:15
If you don't want `_` as a wildcard then `field like 'xyz_abc'` should be `field = 'xyz_abc'`. `LIKE` isn't useful without at least one wildcard. — Sam, Dec 11 '12 at 23:15
Thanks, but the column is case insenstive. The source data could be any case eg, 'Xyz_abc' or 'xyz_Abc' there will however only ever be ONE lower('XyZ_AbC') in the column. I'm trying to avoid writing a patch release to modify the database to convert everything to lower case. — Rowan Smith, Dec 11 '12 at 23:27

score 17 · Accepted Answer · edited Dec 12 '12 at 07:59

17

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.

edited Dec 12 '12 at 07:59

CL.

answered Dec 11 '12 at 23:30

Brigham

1 Answers1