SQLite string contains other string query

Question

How do I do this?

For example, if my column is "cats,dogs,birds" and I want to get any rows where column contains cats?

edit your question with a set of sample data. It's hard to determine exactly what you're asking for. — p.campbell, Aug 17 '10 at 02:35
Seems like you can also use the CONTAINS command but its implementation doesn't appear to be standardized and I couldn't get it to work using SQLite; however, there is documentation for it at [Oracle][1] and [Microsoft][2] and it was supposedly faster according to another [thread][3] but you may need to use another type of SQL instead of SQLite — rfii, Jan 20 '21 at 21:28

score 257 · Accepted Answer · edited Nov 18 '16 at 13:15

257

Using LIKE:

SELECT *
  FROM TABLE
 WHERE column LIKE '%cats%'  --case-insensitive

edited Nov 18 '16 at 13:15

Maksim Turaev

answered Aug 17 '10 at 02:17

OMG Ponies

5

Keep in mind that this isn't suitable where the string you're searching for is variable or contains a special character such as `%`. – Sam May 24 '13 at 01:32
14

if "cats" is a field from an other table, you could use `'%'||table2.field||'%'`as the like criteria – Stéphane Ch. May 12 '16 at 10:14

score 131 · Answer 2 · edited Apr 25 '16 at 10:23

131

While LIKE is suitable for this case, a more general purpose solution is to use instr, which doesn't require characters in the search string to be escaped. Note: instr is available starting from Sqlite 3.7.15.

SELECT *
  FROM TABLE  
 WHERE instr(column, 'cats') > 0;

Also, keep in mind that LIKE is case-insensitive, whereas instr is case-sensitive.

edited Apr 25 '16 at 10:23

Giacomo Catenazzi

answered May 24 '13 at 01:37

Sam

16

Which means that your minSdkVersion should be 21 (5.0-Lollipop) – Maksim Turaev Nov 18 '16 at 08:30
3

@WilliamEntriken In my own test, `instr(...)` is a little bit faster (0.32s vs 0.34s). You can use `.timer on` in SQLite to measure the execution time. – Arnie97 Sep 05 '19 at 07:58

2 Answers2