SQLite string contains other string query

128
votes

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?

2
sqlsqlite
edit your question with a set of sample data. It's hard to determine exactly what you're asking for. - p.campbell
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

2 Answers

222
votes

Using LIKE:

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

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.