SQL unordered wildcard search (variable number of terms)

Question

If want to search for a description which contains the words "hex" and "nut" I would use a query like this:

cursor.execute("SELECT description FROM %s WHERE description LIKE %?% AND
    description LIKE %?%" % table_name , ["hex", "nut" ])

But what if the user wants to now search for a description that contains 5 terms?

Previously in pure sql I had written something like this to generate my results:

base = """
SELECT description FROM {0}
WHERE
""".format(table_name)
command = base
# Adding "AND" for each term
for i in range(len(search_terms)):
    # Last portion of the SQL query
    if i == (len(search_terms) - 1):
        command += " description LIKE '%{0}%'".format(search_terms[i])
    else:
        command += " description LIKE '%{0}%' AND ".format(search_terms[i])
command = command.replace("\n", "")
cursor.execute(command)

However I was hoping there was a better way of doing this with sqlite and sql queries. I know that I could load all the data onto memory and use pure Python to achieve what I want but I want to SQL. So is there an elegant and safe way of doing this?

I'm new to stackoverflow and SQL so let me know if I asked my question wrong.

Parfait · Accepted Answer · 2016-11-19 20:08:22Z

0

Consider a list comprehension with Pythons's str.join:

table_name = 'prices'                                    # EXAMPLE
command = """ SELECT description FROM {0} WHERE """.format(table_name)

search_terms = ['hex', 'nut', 'screw', 'bolt', 'nail']   # EXAMPLE
where = ' AND '.join(["description LIKE '%?%'" for i in search_terms])

command = command + where    
print(command)
#  SELECT description FROM prices WHERE description LIKE %?% AND description LIKE %?%
#         AND description LIKE %?% AND description LIKE %?% AND description LIKE %?% 

cursor.execute(command, search_terms)                    # PARAMETERIZED QUERY

edited Nov 19, 2016 at 20:08

answered Nov 19, 2016 at 16:39

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arash Outadi Over a year ago

Tried this answer, it looks really slick but for some reason I get `sqlite3.OperationalError: near "%": syntax error

Parfait Over a year ago

Whoops! Forgot the single quotes across literal containing the wildcard operators. See edit. I quickly grabbed your first cursor.execute() code believing it was fully valid.

Collectives™ on Stack Overflow

SQL unordered wildcard search (variable number of terms)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related