SQLite select where column does not contain certain data

Question

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';

Are they strings? It certainly looks like they are. – Andrew Nov 11 '13 at 00:05 — Andrew, Nov 11 '13 at 00:05

score 6 · Answer 1 · answered Nov 11 '13 at 00:02

6

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%'

answered Nov 11 '13 at 00:02

Ann L.

13,760
5
35
66

Thank you that worked perfectly. What do the percentage signs do? – user2929075 Nov 11 '13 at 02:41
1

This works ONLY for numbers from 0 to 9. This query will ignore "2,3,11" though it shouldn't. – SqliteDog Nov 11 '13 at 09:27
How to avoid this. Change the format of the second column. For example, from "1,2,4" to "(1)(2)(4)". And change the query condition to "NOT LIKE "%(1)%". – SqliteDog Nov 11 '13 at 09:31
The percentage signs are wildcards. They indicate that the `LIKE` keyword should ignore everything before and after the 1. – Ann L. Nov 11 '13 at 15:18
Try runnning this query: "SELECT '4,5,11' NOT LIKE '%1%'". It will return 0 which is not that OP expected. – SqliteDog Nov 12 '13 at 08:05
@chuck.p.lee I agree. The other answer is a better solution. – Ann L. Nov 12 '13 at 13:01

score 2 · Answer 2 · answered Nov 11 '13 at 10:14

2

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.

answered Nov 11 '13 at 10:14

LS_ᴅᴇᴠ

10,823
1
23
46

SQLite select where column does not contain certain data

2 Answers2