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
– p.campbell, Commented Aug 17, 2010 at 2: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
– rfii, Commented Jan 20, 2021 at 21:28

Maksim Turaev · Accepted Answer · 2016-11-18 13:15:23Z

279

Using LIKE:

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

edited Nov 18, 2016 at 13:15

Maksim Turaev

4,3951 gold badge31 silver badges46 bronze badges

answered Aug 17, 2010 at 2:17

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sam Over a year ago

Keep in mind that this isn't suitable where the string you're searching for is variable or contains a special character such as %.

Stéphane Ch. Over a year ago

if "cats" is a field from an other table, you could use '%'||table2.field||'%'as the like criteria

Giacomo Catenazzi · Accepted Answer · 2016-04-25 10:23:24Z

140

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, 2016 at 10:23

Giacomo Catenazzi

9,6832 gold badges31 silver badges37 bronze badges

answered May 24, 2013 at 1:37

Sam

42.8k39 gold badges184 silver badges232 bronze badges

2 Comments

Maksim Turaev Over a year ago

Which means that your minSdkVersion should be 21 (5.0-Lollipop)

Arnie97 Over a year ago

@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.

Collectives™ on Stack Overflow

SQLite string contains other string query

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related