Reputation: 5629
I might be asking a silly question, but I really need to know. Let's imagine a query like this:
c.execute("SELECT * FROM papers WHERE topic_simple LIKE '%monitor%'")
This query will return all the items with monitor in the field topic_simple, including "monitoring", "monitorate", or whatever.
If I do:
c.execute("SELECT * FROM papers WHERE topic_simple LIKE 'monitor'")
The query returns nothing, not even the items with "monitor" (as a simple word) are returned. And that bothers me, I don't understand why.
I use both the sqlite from PyQt and from sqlite3, and I have the same result.
Is there a way to get the items with "monitor" (as a simple word, whith nothing behind or in front of) with a LIKE query ?
EDIT: here is what a typical topic_simple looks like:
we report that a tree like polymer of lysine is able to form a multi ligand complex with a fluorescently labelled peptide leading to the almost complete extinction of the optical signal that can be restored upon the introduction of heparin this simple system allows for the first time the turn on fluorescent sensing of the anticoagulant in human blood at clinically relevant levels monitoring clinical levels of heparin in human blood samples with an indicator displacement assay
Upvotes: 0
Views: 53
Reputation: 5629
Ok thanks to you guys, I figured out why it didn't work. I thought LIKE was an equivalent to something like CONTAINS.
If I want to return all the items containing the word "monitor", and strictly monitor, not monitoring or anything else, the query must be:
SELECT * FROM papers WHERE topic_simple LIKE '% monitor %'
Note the spaces between monitor and %.
Upvotes: 0
Reputation: 2977
Check the data to see if there are any spaces in the fields that seem to contain just monitor
. There are probably trailing or leading spaces that you can't see in there, making the LIKE comparison return nothing
Upvotes: 1
Reputation: 6087
You have to quote your value, as any other value. Currently, you're first query is a lookup for a field called %monitor%
and you compare with the field monitor
.
You should just use "
-quotes:
c.execute('SELECT * FROM papers WHERE topic_simple LIKE "%monitor%"')
For readability i replaced the other "
to '
.
Upvotes: 1
Reputation: 360922
foo LIKE bar
is the exact equivalent of foo = bar
. If LIKE monitor
doesn't return anything, then your field value must have something ELSE in there, like [space]monitor
or monitor[space]
. Anything that'd throw off a straight equality test.
Try
SELECT length(topic_simple) FROM your table WHERE topic_simple LIKE '%monitor%';
If you DON'T get 7
as the result for where "monitor" should be by itself, then you've got extra characters in the field.
Upvotes: 1