Replacing Substring of Character Data
Retrieving Data with Queries
2-18 Oracle Database 2 Day Developer's Guide
...
107 rows selected
This result set shows the
first_name
values abbreviated to an initial, and the
phone_number
values without the leading area code component.
You can use
REPLACE
, in combination with
SUBSTR
, to replace a specific substring if
you know its relative location in the character data.
, you use
SUBSTR
in the
WHERE
clause to replace the abbreviation for a
job code.
Example 225 Replacing Substring of Character Data
SELECT SUBSTR(first_name, 1, 1) || '. ' || last_name "Name",
REPLACE(job_id, 'SH', 'SHIPPING') "Job"
FROM employees
WHERE SUBSTR(job_id, 1, 2) = 'SH';
The results of the query appear.
Name Job
----------------------- ------------------------
W. Taylor SHIPPING CLERK
J. Fleaur SHIPPING_CLERK
M. Sullivan SHIPPING_CLERK
...
20 rows selected
This result set shows the
first_name
values abbreviated to an initial, and the
job_
id
values were replaced.
Using Datetime Functions
Oracle Database has data functions for manipulating and calculating date and time
data, including interval functions.
In
, you will determine the duration of employment in a particular job
for those employees who have switched to a different position. Note that the names
are not unique because employees may hold more than two different positions over
time. See Oracle Database SQL Language Reference for information about the
MONTHS_
BETWEEN
function.
Example 226 Determining the Number of Months Between Dates
SELECT e.first_name || ' ' || e.last_name "Name",
TRUNC(MONTHS_BETWEEN(j.end_date, j.start_date)) "Months Worked"
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY "Months Worked";
The results of the query appear.
Name Months Worked
---------------------------------------------- -------------
Jonathon Taylor 9
Payam Kaufling 11
See Also:
Oracle Database SQL Language Reference for information on
character SQL functions
