CODEWITHSUNDEEP
sqlsqlitecontain
user2929075
user2929075

Reputation: 71

SQLite select where column does not contain certain data

ok so I have a table that looks something like this...(using pipe symbol to separate columns)

example1 url|0
example2 url|0
example3 url|1
example4 url|1,2
example5 url|1,3,6

What I am trying to do is to select all rows where column 2 does NOT contain a 1.

I cannot use != because if you look all but one of those would return data because the last 2 rows don't equal 1. I tried scouring SQLite documentation for how to go about writing the statement, but I can't find it. This is what I have so far.

select * from table_name where table_column_name[something needed here]'1';

Upvotes: 5

Views: 12933

Answers (2)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11191

For matching a list item separated by ,, use:

 SELECT * FROM table WHERE `,`||column||`,` NOT LIKE '%,1,%';

this will match entire item (ie, only 1, not 11, ...), whether it is at list beginning, middle or end.

Upvotes: 2

Ann L.
Ann L.

Reputation: 13975

Give that "1,2,3" et al are strings, you probably need the LIKE keyword. 

 select * from table_name where table_column_name NOT LIKE '%1%'

Upvotes: 7

Related Questions