Concatenating Character Data
Retrieving Data with Queries
2-16 Oracle Database 2 Day Developer's Guide
NEENA kochhar Nkochhar
LEX de haan Ldehaan
To produce information from two separate columns or expressions in the same
column of the report, you can concatenate the separate results by using the
concatenation operator,
||
. Note also that in
4-way join operation. This result set shows that the simple concatenation function in
column
Name
listed the
last_name
value immediately after the
first_name
value,
while the nested concatenation function in column
Location
separated the
city
and
country_name
values.
Example 221 Concatenating Character Data
SELECT e.first_name || ' ' || e.last_name "Name",
l.city || ', ' || c.country_name "Location"
FROM employees e, departments d, locations l, countries c
WHERE e.department_id=d.department_id AND
d.location_id=l.location_id AND
l.country_id=c.country_id
ORDER BY last_name;
The results of the query appear.
Name Location
----------------------------- --------------------------------------------
Ellen Abel Oxford, United Kingdom
Sundar Ande Oxford, United Kingdom
Mozhe Atkinson South San Francisco, United States of America
...
106 rows selected
You can use
RTRIM
and
LTRIM
functions to remove characters (by default, spaces)
from the beginning or the end of character data, respectively. The
TRIM
function
removes both leading and following characters. In
, you use a type
conversion function,
TO_CHAR
. This result set shows that all employees without a
leading
M
in their
last_name
values, the
MAN
missing from the end of the
job_id
values, and the leading
0
is missing from the
date_hired
values.
Example 222 Trimming Character Data
SELECT LTRIM(last_name, 'M') "Last Name",
RTRIM(job_id, 'MAN') "Job",
TO_CHAR(TRIM(LEADING 0 FROM hire_date)) "Hired"
FROM employees
WHERE department_id=50;
The results of the query appear.
Last Name Job Hired
------------------------- ---------- ---------
Weiss ST_ 18-JUL-96
Fripp ST_ 10-APR-97
Kaufling ST_ 1-MAY-95
Vollman ST_ 10-OCT-97
ourgos ST_ 16-NOV-99
...
ikkilineni ST_CLERK 28-SEP-98
Landry ST_CLERK 14-JAN-99
arkle ST_CLERK 8-MAR-00
...
