2

I use a simple 1-table SQLite database for some data. I retrieve the relevant records by various SQL statements, for example

select * from mydb where C1 = "rc" order by P, Do, W1, W2, W3

All these work fine. But the following query will not find any record:

select * from mydb where Do = "h" order by P, Do, W1, W2, W3

In spite of the fact that I have got a dozen relevant records in my db.

Now when I manually edit any field "Do" in one of the relevant records, replacing the "h" by another letter, e.g. "i" (without the quotes of course), then the above query, with "i" instead of "h", will find that record, and when I manually change back the "i" to an "h" in the field and in the query, the query will again find nothing.

I have tried the above sql query in several sqlite frontends, with identical results: with other letters, the query finds and displays the relevant records, with "h" instead, for the same records, nothing.

This sounds like a joke, but it isn't. I do not have the slightest idea what this may cause. Any idea, anyone?

EDIT: Thinking about it, %H is "hour" in SQLite, and HH:MM is hour/minutes, but how could these codes affect a single "h"? A bug in SQLite? So I also have tried now with a single "d" (for possible "day") and a single "m" (for possible "minute"), but they work fine, i.e. the records are retrieved... while those with "h" in the same cell instead are not...

ABA
  • 23
  • 3
  • Use single quotes when querying for a string. Try querying with `LIKE` (`where Do LIKE '%h%'`) to make sure this is not because of some unexpected whitespace around the `h`. – Glenn Apr 09 '17 at 22:26
  • Bingo. My double quotes have worked fine everywhere, and there are no leading/trailing whitespaces, but my queries with single quotes work as well, and even with this "h" or 'h' as it is now. So what would be of interest now is, why are double/single quotes interchangeable, whilst with a single "h" alone (?), they are not? – ABA Apr 09 '17 at 22:36
  • That "%H" for "hour" is very probably the cause of this, the SQLite developers having foreseen the 'h' or 'H' cases, with and without "%", but have left out the same differentiation with double quotes, so that SQLite mixes them up with an (empty) hour value. I changed all my double quotes to single ones, to avoid further problems of this sort, and will happily accept your answer if you put your single-quotes hint into it. Thank you! – ABA Apr 09 '17 at 22:56
  • [1](http://stackoverflow.com/a/25141338/5045688), [2](https://sqlite.org/lang_keywords.html), [3](https://sqlite.org/faq.html#q24). – Alexander Petrov Apr 10 '17 at 00:03
  • By that one link to keywords, you want to say that by not quoting identifiers, as in my code above, I risk similar problems when I inadvertently name a column or other by a keyword. I got that, too. Thank you again, Alexander! – ABA Apr 10 '17 at 19:29

1 Answers1

0

Single quotes are used for string values, double quotes are used for table and column names.

For compatibility with MySQL, SQLite allows both kinds of quotes in both contexts, but only when the meaning is unambiguous. So "h" will be interpreted as a column name, and only when such a column does not exist, as a string value.

Always use single quotes for strings.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thank you, and I DO have a column "H", so this has been the cause, not some "hour" code as I had mused, not having been aware of this single vs. double quotes thing in sql; now only I'm becoming aware of it, my fault (since my faulty double quotes had worked fine before, I didn't even see the problem). (EDIT: Sorry, my upvote was not counted, me not having the necessary 15 points for that.) Also, many thanks, for the 3 links, to Alexander Petrov! – ABA Apr 10 '17 at 19:16