0

I have a query:

SELECT mt.* FROM myTable as mt
WHERE mt."colName" IN ("PM")

and it always returns zero rows. But, if I change it so that the string is in single quotes:

SELECT mt.* FROM myTable as mt
WHERE mt."colName" IN ('PM')

then it returns the desired rows.

Clue: myTable has a column named "PM".

So, if I search for rows with the string "ABC" in column "colName", it will find rows. But if I add a column named "ABC", then that query also fails unless I specify ('ABC').

Why?

Any answer/solution should take into account that the IN list will be of arbitrary length and will include strings with embedded quotes (both single and double).

Also, while this example is for SQLite, my query also has to work for Postgres.

mwk
  • 71
  • 1
  • 5
  • See https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted – Shawn May 08 '20 at 03:20
  • `"PM"` refers to a column, `'PM'` is a string constant in (standard) SQL –  May 08 '20 at 07:19
  • Thanks. I agree that I was doing it wrong. However, I still don't know how to do it "right". My python code is passed a List of strings, some of which may contain single quotes. That is why I had been wrapping them with double quotes. So, what is the "right" way to do this? My code to generate a WHERE/IN clause is: ``` sqlClause = "WHERE foo IN " + '("' + '","'.join(myList) + '")' ``` Result: ``` WHERE foo IN ("hello'world", "goodbye", ...etc. etc.) ``` That wrapped all strings in double quotes but would not have worked for strings with embedded double quotes. – mwk May 08 '20 at 13:35

2 Answers2

0

In SQL, strings are delimited by single-quotes (e.g. 'foo'), whereas double quotes often delimit object names (depends on RBDMS implementation though). More information here https://stackoverflow.com/a/1992331/1812262

For SQLite-specific details, you can find relevant information about your problem on the official documentation:

If you want to use a keyword as a name, you need to quote it. There are four ways of quoting keywords in SQLite:

  • 'keyword' A keyword in single quotes is a string literal.
  • "keyword" A keyword in double-quotes is an identifier.
  • [keyword] A keyword enclosed in square brackets is an identifier. > This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
  • `keyword` A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.
michaeldel
  • 2,204
  • 1
  • 13
  • 19
0
SELECT mt.* FROM myTable as mt
WHERE PM  IN ('ABC');

If you have a column pm, query a row which has a string ABC in column name pm. The above code is suitable for it

https://www.sqlitetutorial.net/sqlite-select/

David Buck
  • 3,752
  • 35
  • 31
  • 35
Karthik
  • 31
  • 2