Multiple snowflake columns are like this """SOME TEXT WITH SPACES"""

No issue when I'm doing a SELECT * FROM MY_TABLE

But I can't figure out how to query the columns with double-quotes...

If I do :

SELECT """SOME TEXT WITH SPACES""" FROM MY_TABLE

I receive

SQL compilation error: error line 1 at position 7 invalid identifier '""SOME TEXT WITH SPACES""'

I've tried adding other double-quotes but I can't find the right combination...

CodePudding user response：

You need to select the column name using quotes because column names are case sensitive if they are created in double quotes.

Example

create or replace table doublequotes (
  seq int,
  """last_name""" string,
  first_name string
  );




insert into doublequotes values (10, 'abcd', 'efgh');
 
select """last_name""" from doublequotes;

o/p: abcd

Please try.

CodePudding user response：

From the snowflake docs

Important

If an object is created using a double-quoted identifier, when referenced in a query or any other SQL statement, the identifier must be specified exactly as created, including the double quotes. Failure to include the quotes might result in an Object does not exist error (or similar type of error).

Also, note that the entire identifier must be enclosed in quotes when referenced in a query/SQL statement. This is particularly important if periods (.) are used in identifiers because periods are also used in fully-qualified object names to separate each object.

Check the columns using Describe View/Table Table/View_Name

Then to select it you should use extra quotes as escape characters.

CodePudding user response：

If you have a table when selected from:

select * from names;

looks likes this:

you need to understand how it was created, which is also how it need to be accessed.

there is the first layer of double quotes to turn off the case insensitivity. Then for each extra layer of wanted double quotes in the output you have to use two double quotes on both sides. Thus tripple qoutes was made and acessed by 7 double quotes:

create or replace table names (no_quote int, 
    "also_no_quote" int, 
    """single_quoutes""" int, 
    """""""triple_quoutes""""""" int);

insert into names values (0,0,1,3);

and thus can be accessed by:

select no_quote, "also_no_quote", """single_quoutes""", """""""triple_quoutes""""""" from names;