Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Literals


Literal, i.e. fixed data, values may be given for any of the data types supported in SQL statements, wherever the term literal appears in the syntax diagrams.

String Literals

A string literal may be represented as a character-string-literal, a national-character-string-literal, or a unicode-character-string-literal.

Note: An empty string (i.e. '') is a defined value. (It is not a null value.)
Note: An empty string (i.e. N'') is a defined value. (It is not a null value.)
Note: An empty string (i.e. U&'') is a defined value. (It is not a null value.)

Character Separators

For character, national-character, unicode and hexadecimal-string-literals, you can use a separator within the literal to join two or more substrings. Separators are described in Special Characters.

This is particularly useful when a string literal extends over more than one physical line, or when control codes are to be combined with character sequences.

Examples

ASCII codes are used for the hexadecimal literals:

String
Value
'ABCD'
ABCD
'Mimer''s'
Mimer's
'data'<LF>'base'
database
X'0D0A09'
<CR><LF><TAB>
X'0D0A'<LF>'09'
<CR><LF><TAB>
U&'Malm\00F6'
Malmö

Note: Since a hexadecimal-string is of type binary, an explicit CAST is required when using a hexadecimal-string as character data. For CAST information, see Assignments.

Numerical Integer Literals

A numerical integer literal is a signed or unsigned number that does not include a decimal point. The sign is a plus (+) or minus (-) sign immediately preceding the first digit.

In determining the precision of an integer literal, leading zeros are significant (i.e. the literal 007 has precision 3).

Examples:
           47
         -125
         +006
            0

Numerical Decimal Literals

A numerical decimal literal is a signed or unsigned number containing exactly one decimal point.

In determining the precision and scale of a decimal literal, both leading and trailing zeros are significant (i.e. the literal 003.1400 has precision 7, scale 4).

Examples:
       4.7
      -3.
    +012.067
       0.0
        .370

Numerical Floating Point Literals

Floating point literals are represented in exponential notation, with a signed or unsigned integer or decimal mantissa, followed by an letter E, followed in turn by a signed or unsigned integer exponent.

The base for the exponent is always 10. The exponent zero may be used. The case of the letter E is irrelevant.

In determining the precision of a floating point literal, leading zeros in the mantissa are significant (i.e. the literal 007E4 has precision 3).

Examples:
 1.3E5    means 130000
 -4e-2    means     -0.04
 +03.3E2  means    330
 0E+45    means      0
 1.53E00  means      1.53

REAL and DOUBLE PRECISION Literals

There is no syntax for specifying a REAL or DOUBLE PRECISION literal directly.

Instead use a numerical literal specifying an integer, decimal or floating point value. This value can be cast explicitly to REAL or DOUBLE PRECISION by using a CAST construct. If the literal is used in a position where a REAL or DOUBLE PRECISION value is expected, an implicit CAST is used.

Examples:
 INSERT INTO TAB(REALCOL) VALUES (20000001); -- Implicit cast
 SET ? = CAST(0.1 as DOUBLE PRECISION); -- Explicit cast
 

Note that values of type REAL or DOUBLE PRECISION have a binary mantissa. It is not always possible to store the exact decimal value in those types. In such cases the nearest value will be used. In both cases above, the literal value will be silently rounded.

DATE, TIME and TIMESTAMP Literals

A literal that represents a DATE, TIME or TIMESTAMP value consists of the corresponding keyword shown below, followed by text enclosed in single quotes ('').

The following formats are allowed:

DATE 'date-value'

TIME 'time-value'

TIMESTAMP 'date-value <space> time-value'

A date-value has the following format:

 year-value  -  month-value  -  day-value
 

A time-value has the following format:

 hour-value  :  minute-value  :  second-value
 

where second-value has the following format:

 whole-seconds-value [. fractional-seconds-value]
 

The year-value, month-value, day-value, hour-value, minute-value, whole-seconds-value and fractional-seconds-value are all unsigned integers.

A year-value contains exactly 4 digits, a fractional-seconds-value may contain up to 9 digits and all the other components each contain exactly 2 digits.

Examples:

DATE '1997-02-19'

TIME '10:59:23'

TIMESTAMP '1998-11-05 19:20:23.4567'

TIMESTAMP '2012-12-31 23:59:30'

Interval Literals

An interval literal represents an interval value and consists of the keyword INTERVAL followed by text enclosed in single quotes, in the following format:

INTERVAL '[+|-]interval-value' interval-qualifier

The interval-value text must be a valid representation of a value compatible with the INTERVAL data type specified by the interval-qualifier, see Interval Qualifiers.

Examples:

INTERVAL '1:30' HOUR TO MINUTE

INTERVAL '1-6' YEAR TO MONTH

INTERVAL '1000 10:20:30.123' DAY(4) TO SECOND(3)

INTERVAL '-199' YEAR(3) **evaluates to -199

INTERVAL '199' YEAR **Invalid : default leading precision is 2

INTERVAL '5.555' SECOND(1,2) **evaluates to 5.55

INTERVAL '-5.555' SECOND(1,2) **evaluates to -5.55

INTERVAL '19 23' DAY TO MINUTE **Invalid : no minutes in literal

Binary Literals

A binary literal represents an binary value, and is specified as a hexadecimal string.

Examples:

X'5A65794B697A'

x'f66c'

Boolean literals

A boolean literal represents a truth value. There are two boolean literals, TRUE and FALSE.

Boolean literals can be used when assigning values and making comparisons, e.g.

 UPDATE methods SET isConstructor = TRUE WHERE methodName = 'PERSON'
 
 DECLARE v_amountPaid,v_amountDue DECIMAL(10,2);
 DECLARE v_isPaid BOOLEAN DEFAULT FALSE;
 
 SET v_isPaid = v_amountPaid >= v_amountDue;
 IF v_isPaid = TRUE THEN
 

In the last example the comparison with TRUE is not needed. The statement can be written as:

 IF v_isPaid THEN
 
Note: Do not enclose boolean literals in string delimiters. 'TRUE' is a string literal, not a boolean literal.

Spatial literals

The spatial data types are implemented as user-defined types, with functions to create instances, and methods to return the values in different formats. For more information, see Mimer SQL Programmer's Manual, Appendix 13, Spatial Data.

Standard Compliance

This section summarizes standard compliance concerning literals.

Standard
Compliance
Comments
SQL-2016
Core
Fully compliant.
SQL-2016
Features outside core SQL
Feature T021, "BINARY and VARBINARY data types".
Feature T031, "BOOLEAN data type".

Mimer SQL extension
The presence of a newline character (<LF>) between substrings in a character- or hexadecimal-string-literal is not mandatory in Mimer SQL.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX