2

I know how to use the LIKE clause to extract rows that have a column that contains a hard-coded substring:

SELECT * FROM userTable WHERE UPPER(column) LIKE UPPER('%this_is_a_hard_coded_string%')

But I can't figure out how to use a variable for the substring instead of a hardcoded substring. Usually, when I make an sqlite3 query with python3, I can set a value equal to a ? and then add an extra argument to the execute method on the cursor; however, that does not seem to work here.

Mr. Hax
  • 195
  • 1
  • 1
  • 10
  • There is another potentially bigger problem here. The pattern `LIKE %substring%` will fire true for _any_ string which has that `substring`, even if the substring is just a fragment of a larger word. You should try to use full text search if possible, it's worth your investment of time. – Tim Biegeleisen Mar 23 '18 at 01:59
  • What's full text search? Also, why is it bad to fire true even when a substring is just a fragment of a larger word. – Mr. Hax Mar 23 '18 at 02:00
  • Read here: https://www.sqlite.org/fts3.html – Tim Biegeleisen Mar 23 '18 at 02:01
  • Oh, I see. Using LIKE is really inefficient. – Mr. Hax Mar 23 '18 at 02:05
  • Right tool for the right job. There is an overhead to a full text index, and it can be large, and maybe on a mobile device you don't want that. Hopefully you can make a better decision now. – Tim Biegeleisen Mar 23 '18 at 02:10
  • Thanks a lot for the help. – Mr. Hax Mar 23 '18 at 02:11
  • But if I did want to use the LIKE command with a variable, how would I go about it? (note: not playing on it; just out of curiosity.) – Mr. Hax Mar 23 '18 at 02:18
  • See here for how to do this: https://stackoverflow.com/questions/3105249/python-sqlite-parameter-substitution-with-wildcards-in-like?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Tim Biegeleisen Mar 23 '18 at 02:20

1 Answers1

1

Try this 

t = ("%"+"this_is_a_hard_coded_string"+"%",)
c.execute('SELECT * FROM userTable WHERE UPPER(column) LIKE UPPER(?)', t)

You can also use a different more readable use of "?".

Refer this answer

https://stackoverflow.com/a/1010804/1471352

or this

https://stackoverflow.com/a/3105370/1471352

srp
  • 560
  • 1
  • 5
  • 14