Basic Language Elements

Examples

-- This is a comment

SELECT * FROM dual;

-- Display the number of rows of table dual
SELECT count(*) FROM dual;

SELECT avg(salary) -- Average salary
FROM staff         -- From table staff
WHERE age>50;      -- Limitation to over 50 years old

/* Any number of comment rows can be
inserted between the delimiters
*/
SELECT * FROM dual;

SELECT /*This section is not evaluated!*/ * FROM dual;

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 ones:

• 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).

For details about the characters included in each of the specified Unicode classes, see the Unicode standard.

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.

Regular identifiers are not case-sensitive but are always stored in UPPERCASE letters in the database.

Examples

Delimited identifiers

Delimited identifiers are quoted, which here means that they are enclosed in (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

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 PCRE 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, are 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);