Identifiers

Identifiers are used to identify or name objects like tables, columns, schemas, and indexes.

There are two kinds of identifiers, regular and delimited.

Regular identifiers are:

  • Not delimited by double quotes

  • Case insensitive

  • Comprised of only letters, digits and the underscore character

Delimited identifiers are:

  • Delimited by double quotes

  • Case sensitive

  • Comprised of letters, digits, and any of the following characters: % & ' ( ) * + , - . / : ; < = > ? [ ]

Use a delimited identifier if your object:

  • Contain blanks

  • Does not start with a letter

  • Is identical to a keyword

  • Is case-sensitive

  • Contains any of the following characters: % & ' ( ) * + , - . / : ; < = > ? [ ]

Examples

 

 

Incorrect Indetifier

Correct Identifier

Explanation

create view table as select

TABLE_NAME,TABLE_TYPE

from information_schema.tables

where table_schema=USER;

create view "table" as select

TABLE_NAME,TABLE_TYPE

from information_schema.tables

where table_schema=USER;

Table is a keyword and can not be used as a regular identifier. If it is used as a delimited identifier, it can specify a view name.  

 

select col1-1 from tab1;

select "col1-1" from tab1;

col1-1 is a numeric operation in select list and can not be used as a regular identifier.  If it is used as a delimited identifier, it can specify a column name.

 

 

CONNX can handle identifiers of up to 128 characters.