This SQL tutorial focuses on SQL String Concatenation in Oracle, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.
This tutorial is a part of several posts explaining how to write basic queries in Oracle. To read additional posts regarding this subject, please use the following links:
- Writing Basic SQL SELECT statements
- SQL Arithmetic Operators
- SQL Column Aliases
- SQL String Concatenation
- SQL DISTINCT keyword
Oracle String Concatenation
Oracle String concatenation allows you to append one string to the end of another string. To display the contents of two columns or more under the name of a single column, you can use the double pipe concatenation operator (||).
For example, to display the product’s name along with its price, use the following Oracle SQL statement:
SELECT product_name || unit_price FROM products
The result :
product_name || unit_price -------------------------- Tea30
Oracle – Concatenate Two Fields with a Space
While in the previous example, the requested result – merging two values from two different columns – has been achieved, the end result is still quite unreadable, as we have no space seperator between the product’s name and its price. Therefore, it is advisable to also concatenate a space (‘ ‘) :
SELECT product_name || ' ' || unit_price FROM products
The result :
product_name || ' ' || unit_price --------------------------------- Tea 30
Using an Oracle Column Alias
To make the result more readable, use an Oracle Column Aliases:
SELECT product_name || ' ' || unit_price AS "ProductDescription" FROM products
The Result :
ProductDescription ------------------ Tea 30
Oracle – Retrieving additional columns
In Oracle, If after or before this concatenation you would like to display an additional separate column, simply use a comma (,):
SELECT product_id, product_name || ' ' || unit_price AS "ProductDescription",
supplier_id
FROM products
The result:
product_id ProductDescription supplier_id ---------- ------------------ ------------- 23 Tea 30 13
Oracle – Concatenate more than two values
In Oracle it is possible to create more complicated concatenations as required:
SELECT 'Product Name : ' || product_name || ', and product price : ' || unit_price AS "P_DETAILS", FROM products
The result :
P_DETAILS ------------------------------------------- Product Name : Tea, and product price : 30
