CODEWITHSUNDEEP
pythonsqlite
JPFrancoia
JPFrancoia

Reputation: 5629

Why LIKE without wildcards does not work

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

Answers (4)

JPFrancoia
JPFrancoia

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

simplycoding
simplycoding

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

tjati
tjati

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

Marc B
Marc B

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

Related Questions