Insert Text Literal and Concatenation in Oracle SQL

Two solid vertical bar || operator is used to concatenate 2 or more strings. Beside, Oracle also provides the CONCAT character function as an alternative to the vertical bar operator just in case there is situation where it is difficult or impossible to control translation performed by operating system or network utilities. This function should be used in applications that will be moved between environments with differing character sets.

Oracle will automatically casts values into types which can be concatenated. As Oracle interprets NULL as the empty (zero-length) character string, it doesn’t return NULL if an operand is NULL, meaning concatenating a zero-length character string with another operand always results in the other operand, so null can result only from the concatenation of two null strings. To concatenate an expression that might be null, use the NVL function to explicitly convert the expression to a zero-length string.

Concatenating two strings results in another character string. If both character strings are of datatype CHAR, the result has datatype CHAR and is limited to 2000 characters. If either string is of datatype VARCHAR2, the result has datatype VARCHAR2 and is limited to 4000 characters. Trailing blanks in character strings are preserved by concatenation, regardless of the strings’ datatypes.

For literal insertion, put the strings between the single quote ‘ in the SQL statement.

Example and Usage:

SELECT 'Name is ' || name FROM table;

Name is whatever_name

SELECT number || ' - ' || description FROM table ORDER BY number

1 – description 1
2 – description 2

Advance Usage:

It’s possible to use Concatenation and Literal Insertion to generate a set of SQL query language automatically, especially when need to perform same operation to lots of tables, i.e dropping a lot of tables. To do this, format the SQL data query language to output the query result in valid SQL format, and spool the SQL query results to a file. Then execute the file that contains SQL statements.

Example:

SELECT 'DROP TYPE ' || type_name || ';'

will generates:

DROP TYPE type_name

that can be run at SQL*Plus by calling the file with @filename. All types that been selected from first SQL statements will be dropped from the database.