A Guide to Character Literals and the Concatenation Operator

In Oracle Database, “character literals” and the “concatenation operator” are fundamental to string manipulation, allowing you to flexibly process and display data. This article provides a comprehensive guide, from basics to applications, using the scott schema.

What is a Character Literal?

A character literal is a fixed string used when handling strings in queries or PL/SQL. In Oracle, character literals are enclosed in single quotes (‘ ‘).

Sample Query

SELECT 'Hello, Oracle!' AS greeting FROM dual;

Execution Result

SQL> SELECT 'Hello, Oracle!' AS greeting FROM dual;

GREETING
--------------
Hello, Oracle!

SQL> SELECT Hello, Oracle! AS greeting FROM dual;
SELECT Hello, Oracle! AS greeting FROM dual
                    *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected  ★Error

Key Point: The single quotes enclosing the character literal are mandatory. Unlike numeric or date literals, string literals are recognized directly as characters.

Character Literals Containing Single Quotes

To include a single quote (‘) within a character literal, you must write two consecutive single quotes (”).

Sample Query

SELECT 'It''s a beautiful day!' AS text_with_quote FROM dual;

Execution Result

SQL> SELECT 'It''s a beautiful day!' AS text_with_quote FROM dual;

TEXT_WITH_QUOTE
---------------------
It's a beautiful day!

SQL> SELECT 'It's a beautiful day!' AS text_with_quote FROM dual;
ERROR:
ORA-01756: quoted string not properly terminated  ★Error

Key Point: By using two consecutive single quotes, Oracle recognizes it as a single literal single quote.

Alternative Quoting (q operator) Mechanism

In Oracle, besides single quotes, you can also use the q operator for “alternative quoting” as a string delimiter. This mechanism improves code readability when using many single quotes.

Basic Syntax

q'[...]'

In the [...] part, you can use any delimiter character. The start and end characters must be a pair (e.g., [ ], ( ), < >, { }).

Sample Query

SELECT q'[It's a beautiful day!]' AS text_with_quote FROM dual;

Execution Result

SQL> SELECT q'[It's a beautiful day!]' AS text_with_quote FROM dual;

TEXT_WITH_QUOTE
---------------------
It's a beautiful day!

Application Example for Complex Strings

Even strings containing many single quotes can be written clearly using the q operator.

SELECT q'<She said, "It's Oracle's best feature!">' AS complex_text FROM dual;

Execution Result

SQL> SELECT q'<She said, "It's Oracle's best feature!">' AS complex_text FROM dual;

COMPLEX_TEXT
---------------------------------------
She said, "It's Oracle's best feature!"

What is the Concatenation Operator?

The concatenation operator (||) is used to combine multiple strings or columns into a single string.

Example 1: Concatenating Fixed Strings

SELECT 'Hello' || ', ' || 'World!' AS concatenated_text FROM dual;

Execution Result

SQL> SELECT 'Hello' || ', ' || 'World!' AS concatenated_text FROM dual;

CONCATENATED_
-------------
Hello, World!

Example 2: Concatenating Column Data

Let’s look at an example that combines the employee name and job title from the EMP table in the scott schema.

SELECT ENAME || ' is a ' || JOB AS employee_info FROM EMP;

Execution Result

SQL> SELECT ENAME || ' is a ' || JOB AS employee_info FROM EMP;

EMPLOYEE_INFO
-------------------------
SMITH is a CLERK
ALLEN is a SALESMAN
WARD is a SALESMAN
JONES is a MANAGER
MARTIN is a SALESMAN
BLAKE is a MANAGER
CLARK is a MANAGER
SCOTT is a ANALYST
KING is a PRESIDENT
TURNER is a SALESMAN
ADAMS is a CLERK
JAMES is a CLERK
FORD is a ANALYST
MILLER is a CLERK

14 rows selected.

The ENAME column (employee name) and JOB column (job title) are concatenated, converting them into a more human-readable format.

The SCOTT sample schema can be imported and used by executing the following:

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger

Example 3: Concatenation with NULL Values

Let’s check the behavior when a column value is NULL while using the concatenation operator.

SELECT ENAME || ' works in ' || NULL AS employee_info FROM EMP;

Execution Result

SQL> SELECT ENAME || ' works in ' || NULL AS employee_info FROM EMP;

EMPLOYEE_INFO
--------------------
SMITH works in
ALLEN works in
WARD works in
JONES works in
MARTIN works in
BLAKE works in
CLARK works in
SCOTT works in
KING works in
TURNER works in
ADAMS works in
JAMES works in
FORD works in
MILLER works in

14 rows selected.

Key Point: When concatenated with NULL, the result is treated as NULL.

Application Example: Formatting

We will combine the employee name and salary, formatting it into a more readable format.

SELECT 'Employee: ' || ENAME || ', Salary: $' || TO_CHAR(SAL, '999,999') AS formatted_info FROM EMP;

Execution Result

SQL> SELECT 'Employee: ' || ENAME || ', Salary: $' || TO_CHAR(SAL, '999,999') AS formatted_info FROM EMP;

FORMATTED_INFO
---------------------------------------
Employee: SMITH, Salary: $     800
Employee: ALLEN, Salary: $   1,600
Employee: WARD, Salary: $   1,250
Employee: JONES, Salary: $   2,975
Employee: MARTIN, Salary: $   1,250
Employee: BLAKE, Salary: $   2,850
Employee: CLARK, Salary: $   2,450
Employee: SCOTT, Salary: $   3,000
Employee: KING, Salary: $   5,000
Employee: TURNER, Salary: $   1,500
Employee: ADAMS, Salary: $   1,100
Employee: JAMES, Salary: $     950
Employee: FORD, Salary: $   3,000
Employee: MILLER, Salary: $   1,300

14 rows selected.

Key Point: The TO_CHAR function is used to format the salary.

Troubleshooting: Common Errors

This section explains errors that may occur when using character literals or the concatenation operator.

Error 1: Mismatched Single Quotes

SELECT 'It's a mistake AS error_example FROM dual;

Error Encountered

SQL> SELECT 'It's a mistake AS error_example FROM dual;
SELECT 'It's a mistake AS error_example FROM dual
             *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Solution: Write two consecutive single quotes or use alternative quoting (q operator).

Error 2: Unexpected Behavior of NULL Values

SELECT 'Name: ' || NULL || 'Missing' AS result FROM dual;

Execution Result

SQL> SELECT 'Name: ' || NULL || 'Missing' AS result FROM dual;

RESULT
-------------
Name: Missing

Solution: Use the COALESCE function to handle NULL values.

SELECT 'Name: ' || COALESCE(NULL, 'Unknown') || 'Missing' AS result FROM dual;

Revised Result

SQL> SELECT 'Name: ' || COALESCE(NULL, 'Unknown') || 'Missing' AS result FROM dual;

RESULT
--------------------
Name: UnknownMissing

Summary

Character literals are fixed strings enclosed in single quotes in Oracle.
The concatenation operator (||) allows easy combination of multiple strings or columns.
To include a single quote, two consecutive single quotes are necessary.
Alternative quoting (q operator) can be used to write complex strings more clearly.
Be aware that concatenating with NULL results in NULL; this can be managed with the COALESCE function.
Formatting is possible using TO_CHAR.

By utilizing these, you can freely customize data display formats. Please try these queries yourself using the scott schema!

[reference]
Oracle Database SQL Language Reference, 19c