How to Add Multiple "Not Like '%%' in the Where Clause of SQLite3

How do I add multiple NOT LIKE '%?%' in the WHERE clause of sqlite3?

If you use Sqlite's REGEXP support ( see the answer at Problem with regexp python and sqlite for how to do that ) , then you can do it easily in one clause:

SELECT word FROM table WHERE word NOT REGEXP '[abc]';

Multiple LIKE in sqlite

If you have SQLite FTS3 and FTS4 Extensions enabled then you can take advantage of Full Text Search (FTS) capabilities. You will need to recreate the p_extra_fields table as a VIRTUAL table. Then you can insert OR between your search terms and use the MATCH operator...

SELECT p.* 
FROM p_extra_fields x
JOIN products p ON p.id = x.product
WHERE x.key IN ('model', 'color', 'make')
AND x.type = '1'
AND x.value MATCH 'foo OR bar'
GROUP BY x.product LIMIT 0, 50;

Good info here also.
Click here to see it in action at SQL Fiddle.

PYTHON SQLITE selecting multiple where conditions that may or may not exist

Probably the easiest way to deal with this is to put all the filter conditions and values into lists, and then only add a WHERE clause if the length of the filters list is non-zero. For example:

query = """SELECT * FROM parts"""
filters = []
values = []
if part_name:
    filters.append("""part_name LIKE ?""")
    values.append(part_name)
...
if len(filters):
    query += ' WHERE ' + ' AND '.join(filters)
c.execute(query, tuple(values))

Note: should your filters ever include OR conditions, you need to parenthesise them when building the query to ensure correct operation i.e.

query += ' WHERE (' + ') AND ('.join(filters) + ')'

How to do NOT in LIKE query in SQLite?

You cannot construct a LIKE pattern that behaves the same as a NOT LIKE pattern; the only special characters are % and _.

That someone else's code is just not capable of doing what you want.

SQLite Select Query Multiple Where Clause Values

From the link:

sqlite

SELECT id, . FROM additionalchunks WHERE id in (? ? ? ? ? .)

works, and works efficiently (with an index) :)

SQLite multiple conditions in WHERE LIKE

What about:

SELECT id FROM test WHERE 
column1 LIKE '%bob%' AND
    (column2 LIKE '%something%' OR
     column2 LIKE '%somethingdifferent%' OR
     column2 LIKE '%somethingdifferent2%')

It's logically equivalent...

You can also use a RegEx: see How do I use regex in a SQLite query?

Sqlite SELECT with multiple conditions

You need a join of PROD_CAT to CATEGORIES:

SELECT NAME 
FROM PRODUCTS
WHERE ID_PROD IN (
  SELECT pc.ID_PROD 
  FROM PROD_CAT pc INNER JOIN CATEGORIES c
  ON c.ID_CAT = pc.ID_CAT
  WHERE c.PRIVATE -- or WHERE NOT c.PRIVATE for public categories
  GROUP BY pc.ID_PROD 
  HAVING COUNT(*) > 5
)

Or, without the operator IN, with joins of all 3 tables:

SELECT p.ID_PROD, p.NAME  
FROM PRODUCTS p
INNER JOIN PROD_CAT pc ON pc.ID_PROD = p.ID_PROD
INNER JOIN CATEGORIES c ON c.ID_CAT = pc.ID_CAT
WHERE c.PRIVATE -- or WHERE NOT c.PRIVATE for public categories
GROUP BY p.ID_PROD, p.NAME 
HAVING COUNT(*) > 5

Using multiple values in SQL query where clause

You can not use != for multiple values for that you should use not in like:

Select Distinct 
    SomeDay.SomeDayID,SomeDay.FolderName,SomeDay.FolderColor 
from 
    SomeDay,SomeDayEvent 
where 
    SomeDay.SomeDayID not in (4,3,2,1);

---

Related Topics