CODEWITHSUNDEEP
databasesqlite
Christopher Thomas
Christopher Thomas

Reputation: 4702

sqlite why select like works and equals does not?

I have a problem with sqlite3 database, I execute the following queries

sqlite> select * from property where link like "http://www.domain.com/huur/den-bosch/appartement-48118689-meester-spoermekerlaan-88/";

I get two rows

17|2014-11-03|Meester Spoermekerlaan 88|http://www.domain.com/huur/den-bosch/appartement-48118689-meester-spoermekerlaan-88/|5237 JZ|Den Bosch|€ 789|3|1

32|2014-11-03|Meester Spoermekerlaan 88|http://www.domain.com/huur/den-bosch/appartement-48118689-meester-spoermekerlaan-88/|5237 JZ|Den Bosch|€ 789|3|1

Then I execute the same query, but using the equality operator, like so

sqlite> select * from property where link="http://www.domain.com/huur/den-bosch/appartement-48118689-meester-spoermekerlaan-88/";

sqlite> (<---- no results??)

I already found a similar answer to mine, however the issue is not the same, my fields are of datatype "text", as you can see here: https://stackoverflow.com/a/14823565/279147

sqlite> .schema property

CREATE TABLE property (id integer PRIMARY KEY AUTOINCREMENT UNIQUE,"date" text,address text,link text,postcode text,city text,price text,rooms text,page integer);

So does anybody have any idea why this would happen? here is my version information

root@s1:/# sqlite3 application.sqlite3

SQLite version 3.7.3

Upvotes: 0

Views: 1694

Answers (4)

Ber&#233;nyi Lajos
Ber&#233;nyi Lajos

Reputation: 11

I had similar issue, in my case the point was, the like is not case sensitive, but the = operator is case sensitive.

Upvotes: 1

ki15686
ki15686

Reputation: 21

I had the same problem. This work around worked for me.

SELECT * from foo WHERE CAST(name AS BLOB) = CAST('bla' AS BLOB);

Upvotes: 2

CL.
CL.

Reputation: 180020

SQLite uses dynamic typing; it does not matter if the link column is declared as text or blob or fluffy bunnies.

LIKE automatically converts its parameters into strings, but the = operator does not.

Fix the program that writes the entries to the database to use the correct data type.

Upvotes: 1

Ben Minton
Ben Minton

Reputation: 118

This is because = is a literal string comparison so it would have to be exactly the same. The like operator is looking for a matching pattern within the string and finds it.

Upvotes: 0

Related Questions