CODEWITHSUNDEEP
sqlsqlite
Joren
Joren

Reputation: 9915

SQLite string contains other string query

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?

Upvotes: 172

Views: 189038

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332581

Using LIKE:

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

Upvotes: 277

Sam
Sam

Reputation: 42387

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.

Upvotes: 138

Related Questions