Multiple words in Where field LIKE

Question

I have a list of values (words) and I want to check if a column in my table contains a value (any value) from the list.

My list can be very long so I want to create it using a for loop, for example:

words = ( 'one', 'two', 'three' )
whereClause=""
a=""
for word in words:
   temp=" item LIKE '%" +word +  " %' or"
   whereClause=whereClause+temp
whereClause = whereClause[17:]  #delete first "item LIKE"
whereClause = whereClause[:-3] #delete last "or"

Now I want to put it in my sql query:

sql= """select name
from table
where item LIKE ? """

cursor.execute(sql, whereClause)
rows=cursor.fetchall()

It's not working, any suggestions?

Do you think I better get all the values of the column "name" using sql query and only then check if values from the list exists using Python? Thanks!

your problem is when you remove your first item like. Simplify things and in your sql variable leave it as 'where ?' and dont remove the first item like — Walter_Ritzel, May 02 '16 at 20:20
Please avoid constructing your `where` clause this way as it exposes you to `SQL injection` attacks. Your best bet is to construct as many `OR item LIKE ?` iterations as the number of elements in `words`. Then pass in the whole `words` array as a sqlParameter at execution time. — Stavr00, May 02 '16 at 20:46
thanks @Stavr00 , but I'm not sure I understand your idea. could you please explain? — user5435739, May 03 '16 at 14:10
@Walter_Ritzel I tried it and got an error "An expression of non-boolean type specified in a context where a condition is expected" — user5435739, May 08 '16 at 11:49
Possible duplicate of [Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) — Stavr00, May 17 '16 at 13:47

score 0 · Answer 1 · answered May 03 '16 at 19:01

0

Not very fluent in Python but here goes:

for word in words:
   whereClause += " OR" if whereClause
   whereClause += " ITEM LIKE ?"
sql= "select name from table where" + whereClause
cursor.execute(sql, words)

answered May 03 '16 at 19:01

Stavr00

3,219
1
16
28

score 0 · Accepted Answer · answered May 08 '16 at 13:27

In case you are still facing issues:

words = ( 'one', 'two', 'three' )
whereClause=""

i = 1
for word in words:
    if i == 1:
        whereClause = " item LIKE '%" +word + "%'"
    else:
        whereClause += " OR item LIKE '%" +word + "%'"
    i += 1

sql= """select name
from table
where {0} """.format(whereClause)

cursor.execute(sql)
rows=cursor.fetchall()

Multiple words in Where field LIKE

2 Answers2