I am working with a malformed database which seems to have double quotes as part of the column names.

Example:

|"Market" |
|---------|
|Japan    |
|UK       |
|USA      |

And I want to select like below

SELECT "\"Market\"" FROM mytable;  /* Does not work */

How does one select such a thing?

CodePudding user response：

The documentation says

[A] delimited identifier or quoted identifier […] is formed by enclosing an arbitrary sequence of characters in double-quotes ("). […]
Quoted identifiers can contain any character, except the character with code zero. (To include a double quote, write two double quotes.)

So you'll want to use

SELECT """Market""" AS "Market" FROM mytable;

An alternative would be

A variant of quoted identifiers allows including escaped Unicode characters identified by their code points. This variant starts with U& (upper or lower case U followed by ampersand) immediately before the opening double quote, without any spaces in between, for example U&"foo". […] Inside the quotes, Unicode characters can be specified in escaped form by writing a backslash followed by the four-digit hexadecimal code point number or alternatively a backslash followed by a plus sign followed by a six-digit hexadecimal code point number.

which in your case would mean

SELECT U&"\0022Market\0022" AS "Market" FROM mytable;
SELECT U&"\ 000022Market\ 000022" AS "Market" FROM mytable;

Disclaimer: your database may not actually have double quotes as part of the name itself. As mentioned in the comments, this might just be the way in which the tool you are using does display a column named Market (not market) since

Quoting an identifier also makes it case-sensitive

So all you might need could be

SELECT "Market" FROM mytable;