sqlite: alias column name can't contains a dot "."

(sorry for my poor english)

If you try this select operation over a sqlite database:

SELECT column AS 'alias 1' FROM table;

You get the expected column name:

alias 1
--------
result 1
result 2

but if your alias contains a dot "." ... you get a wrong column name:

SELECT column AS 'alias.1' FROM table;

1
--------
result 1
result 2

(all behind the dot is ommited in the column name)

Wow... It's weird...

anyone can help me?

thank you very much

UPDATE:

maybe it's just a bug in SQLiteStudio (the software where I'm testing my queries) and in QT (they both doesn't开发者_如何学Go expect dots in alias names but sqlite does)

Enclose your alias in double quotes.

SELECT 'test' AS "testing.this"

Output:

| testing.this |
  test

Updated: Double quotes are used to enclose identifiers in SQL, not single quotes. Single quotes are only for strings. In this case you are trying to ensure that "testing.this" is used as is and not confused as testing.this (testing table this column).

http://www.sqlite.org/faq.html#q24

Use backticks

SELECT column AS `alias.1` FROM table;

Or double quotes (ANSI standard) per the other answer

SELECT column AS "alias.1" FROM table;

Both verified in SQLite Manager for FireFox

Definitely working properly:

C:\Windows>sqlite3.exe
SQLite version 3.7.8 2011-09-19 14:49:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT 'hello' AS 'alias.1';
alias.1
----------
hello
sqlite>

If you're using the SQLite 3 then the following query works just fine with various types used for the Alias column names.

See the result below the query:

select '1' as 'my.Col1', '2' as "my.Col2", '3' as [my.Col3], '4' as [my Col4] , '5' as 'my Col5' 

I've found a "fix"...

SELECT column AS '.alias.1' FROM table;

alias.1
--------
result 1
result 2

just another dot in the begining...

of course I don't like this solution... any other idea??

Please try below, it works on Hive

select 1 as `xxx.namewith.dot`

xxx. means any word you want to input with dot notation on latest

namewith.dot means any alias name with dot notation on it