Unlocking the Power of ||: The String Concatenation Operator in Oracle

Concatenating variables:

-- Declare two variables
DECLARE
  first_name VARCHAR2(20) := 'John';
  last_name VARCHAR2(20) := 'Doe';
  full_name VARCHAR2(40);
BEGIN
  -- Concatenate the first and last name variables
  full_name := first_name || ' ' || last_name;
  
  -- Display the result
  DBMS_OUTPUT.PUT_LINE('Full name: ' || full_name);
END;
/

This code snippet declares two variables, first_name and last_name, and assigns values to them. Then, it uses the concatenation operator to combine these variables along with a space (' ') into a new variable, full_name. Finally, it displays the concatenated string.

Concatenating strings and literals:

SELECT 'Hello, ' || name || '! How are you?' AS greeting
FROM customers;

In this example, the SQL query selects a column named name from the customers table. It then uses the concatenation operator to create a new column alias, greeting, by combining a literal string ('Hello, '), the name column, an exclamation point ('!'), and another literal string (' How are you?').

Concatenating with NULL values:

It's important to note that concatenating a string with a NULL value will result in a NULL value. For example:

SELECT 'Name: ' || name || ' (city: ' || city || ')'
FROM employees
WHERE city IS NULL;

This query will return NULL for the entire concatenated string in any row where the city column is null.

Additional points:

• The resulting data type of the concatenation operation depends on the data types of the operands being concatenated.
• If both operands are of type CHAR, the result will be CHAR with a maximum length of 2000 characters.
• Concatenating a CLOB data type with any other data type will result in a temporary CLOB.