Basic language elements

Regular identifiers

Regular identifiers are unquoted.

As defined in the SQL standard, Exasol only allows a subset of the Unicode character set in regular identifiers. The first character of an SQL identifier is more restricted than the following characters.

First character:

All letters of Unicode classes Lu (uppercase letters), LI (lowercase letters), Lt (title case letters), Lm (modifier letters), Lo (other letters), and Nl (letter numbers).

Following characters:

All classes allowed for the first character, plus all letters of Unicode classes Mn (non-spacing marks), Mc (spacing combination marks), Nd (decimal numbers), Pc (connectors punctuations), Cf (formatting codes), and the Unicode character U+00B7 (middle dot).

When using the simple ASCII character set, a regular identifier may start with letters of the set {a-z, A-Z} and may further contain letters of set {a-z, A-Z, 0-9,_}.

A reserved word cannot be used as a regular identifier. For more information, see Reserved words.

The database converts regular identifiers to UPPERCASE when storing them in the database.

Examples:

Delimited identifiers

Delimited identifiers are quoted, which here means that they are delimited by double quotation marks ". All characters except a dot (.) are allowed in a delimited identifier. In the case of users and roles you are allowed to use a dot, for example, in email addresses. This is because users and roles do not conflict with schema-qualified names. For more details, see Schema qualified names.

To use double quotation marks within a name you must include two successive double quotation marks. For example, "ab""c" indicates the name ab"c.

Except in users and roles, delimited identifiers are case-sensitive when stored in the database.

Examples:

Comparison of identifiers

Exasol preserves case when storing identifiers, but the system value SQL_IDENTIFIER_COMPARISON controls how identifiers are compared.

If SQL_IDENTIFIER_COMPARISON is set to CASE SENSITIVE then comparison is case sensitive, which means that the identifiers "Exasol" and "EXASOL" are different. This is the behavior defined by the SQL Standard and the default behavior in Exasol.

If SQL_IDENTIFIER_COMPARISON is set to IGNORE CASE then comparison ignores case, which means that "Exasol" and "EXASOL" match. If more than one object matches (because they differ only in case and were created when the system value was CASE SENSITIVE) then it will raise the error the matching objects … are ambiguous because of case-insensitive comparison.

In statements where a name with a different case matched and the result depends on the name chosen, Exasol will use the most recent name. For example:

• For CREATE OR REPLACE statements, the name used in the statement will replace that of the original object. For example, if there is a view called "V" and the user does CREATE OR REPLACE VIEW "v" AS ...; then the new view will be called "v".

• When a select list element is a column reference, the name in the result set will be that of the column reference, not the name of the original column in the FROM clause. For example, SELECT "One" FROM VALUES (1) AS "ONE"; will give the result column "One".

SQL_IDENTIFIER_COMPARISON does not affect the following object types:

Passwords and consumer group names

These remain case-sensitive regardless of the system value.

User and role names

These remain case-insensitive regardless of the system value.

Considerations when changing the comparison method

Examples:

alter system set SQL_IDENTIFIER_COMPARISON = 'CASE SENSITIVE';
create table "MyTable" (C int);

-- Observed: Success
select * from "MyTable";

-- Observed: Error 'object "mytable" not found'
select * from "mytable";

alter system set SQL_IDENTIFIER_COMPARISON = 'IGNORE CASE';

-- Observed: Success
select * from "MyTable";
-- Observed: Success
select * from "mytable";

alter system set SQL_IDENTIFIER_COMPARISON = 'CASE SENSITIVE';
create table "MYTABLE" (C int);

-- Observed: Success
select * from "MyTable";

alter system set SQL_IDENTIFIER_COMPARISON = 'IGNORE CASE';

-- Observed: Error 'the matching objects "MyTable" and "MYTABLE" are ambiguous because of case-insensitive comparison'
select * from "MyTable";

Schema qualified names

A schema qualified name consists of the schema name and the object name, separated by a dot. Using schema qualified names enables access to objects that are not located in the current schema. If the schema name is omitted, the current schema is implied.

Examples:

SELECT my_column FROM my_schema.my_table;
SELECT "my_column" FROM "my_schema"."my_table";
SELECT my_schema.my_function(my_column) from my_schema.my_table;

Reserved words

Reserved words such as the keyword SELECT cannot be used as regular identifiers. If you want to create a table with the name SELECT, the name must be delimited with double quotation marks. Note that the name will then be case-sensitive, which means that SELECT as a table name is different from a table name such as Select or seLect.

A list of the reserved words in Exasol can be found in the EXA_SQL_KEYWORDS system table.

The compiled code for PCRE2 by default uses an offset of 2 bytes stored in big-endian order. These are used, for example, to link from the start of a subpattern to its alternatives and its end. The use of 2 bytes per offset limits the size of the compiled pattern to 64K.

Examples:

In the description of the corresponding scalar functions and predicates, you can find examples of regular expressions within SQL statements. The following examples demonstrate the general expressions: 

The expressions in these examples are simplified to provide clarity. Expressions used in a production environment, for example, an expression for identifying valid email addresses, can be much more complex.

Pattern elements

You can use the following elements in regular expressions:

SELECT 
    col1, 
    col1 regexp_like '(?i:hello)world' as match_flag
FROM 
    values 'helloworld', 'Helloworld', 'HellOworld', 'helloWorld', 'helloWORLD' as t1(col1);