2.2 Literal Values, Concatenation, and Quote Operators

Literal Values, Concatenation, and Quote Operators

SQL queries frequently require combining raw database column values with static text, formatting numbers into descriptive sentences, and handling embedded punctuation such as apostrophes. Oracle SQL provides powerful operators to synthesize dynamic text outputs directly within the SELECT projection list.

---

1. Literal Values in SQL SELECT

A literal value (or constant) is an explicit data value that is hard-coded into a SQL statement. Unlike column names, which evaluate to different values for each row, a literal evaluates to the exact same constant value across every row returned by the query.

+-----------------------------------------------------------------------------+
|                        TYPES OF LITERALS IN ORACLE SQL                      |
|                                                                             |
|   1. CHARACTER LITERALS: Enclosed in SINGLE quotes (Case-Sensitive)         |
|      'Manager', 'Oracle Database 19c', 'Dept: '                             |
|                                                                             |
|   2. NUMERIC LITERALS: Unquoted raw numbers (Integers or Decimals)          |
|      100, 0.05, -4500, 3.14159                                              |
|                                                                             |
|   3. DATE LITERALS: Single-quoted strings in default NLS format or ANSI      |
|      '17-JUN-2003', DATE '2026-08-15'                                      |
+-----------------------------------------------------------------------------+

Rules for Literals by Data Type

-- Projecting character and numeric literals alongside table columns
SELECT last_name, 'works in department', department_id, 1000 AS bonus_base
FROM employees
WHERE department_id = 90;

Simulated Result:

LAST_NAME                 'WORKSINDEPARTMENT' DEPARTMENT_ID BONUS_BASE
------------------------- ------------------- ------------- ----------
King                      works in department            90       1000
Kochhar                   works in department            90       1000
De Haan                   works in department            90       1000

---

2. The Concatenation Operator (||)

The concatenation operator consists of two vertical bar characters (||). It connects two or more character strings, column values, numeric expressions, or date values together to create a single composite character expression.

Key Mechanics of Concatenation:

• Return Data Type: The result of a concatenation operation is always a character string (VARCHAR2).
• Multiple Operands: You can chain as many concatenation operators together as required in a single expression.
• Implicit Conversion: If numbers or dates are concatenated, Oracle automatically converts them to character strings using session defaults.

SELECT first_name || ' ' || last_name AS "Full Name",
       last_name || ' earns $' || salary || ' per month.' AS "Salary Report"
FROM employees
WHERE employee_id = 100;

Simulated Output:

Full Name            Salary Report
-------------------- --------------------------------------------------
Steven King          King earns $24000 per month.

---

3. Concatenation with NULL Values (Critical Oracle Distinction!)

One of the most important concepts tested on the Oracle 1Z0-071 exam is the behavioral difference between arithmetic operators and the concatenation operator when encountering a NULL value.

+-----------------------------------------------------------------------------+
|                   NULL IN ARITHMETIC VS. NULL IN CONCATENATION              |
|                                                                             |
|   1. ARITHMETIC WITH NULL:                                                  |
|      salary + commission_pct  ===>  5000 + NULL  ===>  NULL (Unknown)       |
|                                                                             |
|   2. CONCATENATION WITH NULL (Oracle treats NULL as ''):                    |
|      'Emp: ' || last_name || ' Comm: ' || commission_pct                    |
|      'Emp: King Comm: ' || NULL  ===>  'Emp: King Comm: '                   |
+-----------------------------------------------------------------------------+

[!IMPORTANT] The Oracle Concatenation Rule: When a NULL operand is concatenated with a character string, Oracle treats the NULL as an empty string (''). The result is the character string itself, NOT NULL. (The only exception is concatenating two NULLs: NULL || NULL evaluates to NULL).

SELECT last_name || ' has commission code: ' || commission_pct AS "Commission Notice"
FROM employees
WHERE employee_id IN (100, 145);

Simulated Output:

Commission Notice
--------------------------------------------------
King has commission code: 
Russell has commission code: .4

Notice that for Steven King (whose commission_pct is NULL), the string does not turn into NULL; instead, the NULL is simply rendered as an empty string at the end of the text.

---

4. The Alternative Quote (q) Operator

The Problem: Embedded Single Quotes / Apostrophes

When a literal text string contains an apostrophe or single quotation mark (e.g., King's salary or Department's goal), placing a single quote inside a single-quoted literal terminates the string prematurely, causing a syntax error:

-- SYNTAX ERROR: ORA-01756: quoted string not properly terminated
SELECT last_name || ' earns ' || salary || ' and that's final!' FROM employees;

Legacy Workaround: Doubling Single Quotes

Traditionally, SQL required developers to place two consecutive single quotes ('') to represent one literal single quote inside text:

-- Legacy method: Doubled single quotes
SELECT last_name || '''s annual compensation is ' || (salary * 12) AS "Report"
FROM employees;

While functional, this approach is difficult to read and error-prone when dealing with complex strings.

The Modern Oracle Solution: The q Operator

Introduced to improve code readability, the Alternative Quote (q) Operator allows you to define your own literal quotation delimiters. The syntax starts with q or Q followed by a single quote, an opening delimiter, the text string, a matching closing delimiter, and a final single quote.

Syntax:
q'[text with any ' quotes inside]'
q'<text with any ' quotes inside>'
q'{text with any ' quotes inside}'
q'(text with any ' quotes inside)'
q'!text with any ' quotes inside!'
q'#text with any ' quotes inside#'

-- Using bracket delimiters [ ]
SELECT q'[Department's Manager]' AS dept_mgr FROM dual;

-- Using exclamation point delimiters ! !
SELECT q'!It's John's turn to present SQL's features!' AS presentation FROM dual;

-- Using curly brace delimiters { }
SELECT q'{Oracle's "SELECT" statement syntax}' AS doc FROM dual;

Delimiter Rules Rigorously Tested on 1Z0-071:

1. Paired Delimiters (Brackets): If you choose [, (, <, or { as the opening delimiter, you MUST use the corresponding closing symbol ], ), >, or } as the closing delimiter.
   • Valid: q'[Oracle's Database]'
   • Valid: q'(Oracle's Database)'
   • Valid: q'<Oracle's Database>'
   • Valid: q'{Oracle's Database}'
   • Invalid: q'[Oracle's Database)' (Mismatched delimiters trigger syntax error)
2. Unpaired Delimiters (Any Single-Byte Character): If you use any non-bracket character (e.g., !, #, |, *, ~, ^, $), the opening and closing delimiters MUST BE IDENTICAL.
   • Valid: q'!Today's Date!'
   • Valid: q'#Father's Day#'
   • Valid: q'|User's Guide|'
   • Invalid: q'!Today's Date#' (Mismatched characters)
3. Prohibited Delimiters: You cannot use whitespace characters (space, tab, carriage return) or the single quotation mark itself (') as a delimiter.
4. Case Insensitivity of the Prefix: Both lowercase q'...' and uppercase Q'...' are fully valid.

---

5. Summary Table of Literal Syntax & Delimiter Rules