Oracle SQL - Constants and Literals

  1. HOME
  2. Oracle SQL
  3. Introduction
  4. Constants and Literals

Introduction

A constant or literal is a fixed value.

We distinguish numbers (numeric constants) and text (alphanumeric constants).

In SQL language, alphanumeric constants (strings) must be placed between single quotation marks (quotes).

Numbers cannot be put between quotes or they will be interpreted as strings.

You can explicitly indicate numeric values as floating point numbers by adding the suffixes f or d to indicate single (float) or double precision, respectively.

Be careful with the decimal period and group separators (commas) in numbers.

The correct interpretation of these characters depends on the value of a session parameter (NLS_NUMERIC_CHARACTERS), and there are some cultural differences.

In SQL, dates and time durations/intervals are special cases.

They are typically specified and represented as alphanumeric constants.

You can prefix the strings with a keyword (DATE, TIMESTAMP, or INTERVAL) and to adhere to a well-defined notation convention.

These are the three options to specify date and time-related constants in SQL:

  • Specify them as alphanumeric constants (strings) and rely on implicit interpretation and conversion by the Oracle DBMS. This is dangerous, because things can go wrong if the actual format parameter for that session is different from the format of the string.
  • Specify them as alphanumeric constants (strings) and use a CAST or TO_DATE conversion function to specify explicitly how the strings must be interpreted.
  • Specify them as alphanumeric constants (strings), prefixed with DATE, TIMESTAMP, or INTERVAL. If you use INTERVAL, you also need a suffix to indicate a dimension, such as DAY, MONTH, or YEAR.

The following table shows examples of using SQL constants.

Type
 Example
Numeric



 42
8.75
8.75F
132
Alphanumeric


 'Jack'
'Joe'
'132'
Dates and intervals



 DATE '2018-02-09'
TIMESTAMP '2018-09-05 11.42.59.00000'
INTERVAL '2' SECOND
INTERVAL '1-3' YEAR TO MONTH

In general, the SQL language is case-insensitive.

Alphanumeric constants (strings) are case-sensitive.

For example, 'Jack' is not equal to 'JACK'.