SELECT WHERE some text, contains square brackets

Question

I have some text column, that have format like

 ["some text", ]

Let this column has name col1. And do

SELECT columname FROM table WHERE col1 = '["some text", ]'

I receive nothing. But with

SELECT columname FROM table WHERE col1 LIKE '["some text", ]'

I receive needed data. I really don't understand why is it so. I want to use =, 'cos I read a lot that LIKE is much slower. And also I really don't understand why is it so.

UPD: As requested more info

CREATE TABLE IF NOT EXISTS fulldata (col1 TEXT, data TEXT);

Insert I do via QSqlQuery with prepare

insertData.prepare("INSERT INTO fulldata(col1 , data) "
                      " VALUES(:col1, :data);");
insertData.bindValue(":col1", currentFilter);

currentFilter is [ "2014-01-14 00:00:00", 2 ]

Select I also do using QSqlQuery with prepare

  selectQuery.prepare("SELECT data FROM fulldata WHERE col1 = :col1 ");
  selectQuery.bindValue(":col1 ", strFilter);

strFilter is also [ "2014-01-14 00:00:00", 2 ]

Also I do select from "Qt SQL Browser" from Qt 5.1 examples, same result as from code.

My SQLite version = 3.7.17

It is strange that you get different results. However, there is really no point in using LIKE without wildcards (i.e. % sign), but if you do the speed should be similar, check: http://stackoverflow.com/questions/404226/sql-like-with-no-wildcards-the-same-as — Mikael Lindqvist, Jan 14 '14 at 22:58
Any leading/trailing whitespace or similar in either bindvalue that would make the binary collator to not match? — laalto, Jan 15 '14 at 07:10

peter.petrov · Answer 1 · 2014-01-14T23:58:44.160

0

Both are returning correct results.

Check here: http://sqlfiddle.com/#!5/4c033/4

I assume you're missing something else, just some detail which you did not mention in the question.

Yes, LIKE is indeed much slower.

EDIT:

Tried it on my local machine, still no issues.

enter image description here

edited Jan 14 '14 at 23:58

answered Jan 14 '14 at 23:07

peter.petrov

38,363
16
94
159

Of course my real tables called in other way. Indeed on your link both variant works. I use SQLite 3, and there I got behavior described in the question – user3136871 Jan 14 '14 at 23:22
@user3136871 That is really weird. Answer updated. Maybe you should update your question with the exact statements you have for 1) table creation; 2) data insertion; 3) data selection. I think it is some detail which you're missing and which is not mentioned in the question. – peter.petrov Jan 14 '14 at 23:50

SELECT WHERE some text, contains square brackets

1 Answers1