Padding Character Data
Retrieving Data with Queries
Querying and Manipulating Data 2-17
arlow ST_CLERK 16-FEB-97
...
allin ST_CLERK 14-JUN-96
...
Philtanker ST_CLERK 6-FEB-00
...
Patel ST_CLERK 6-APR-98
...
atos ST_CLERK 15-MAR-98
Vargas ST_CLERK 9-JUL-98
Taylor SH_CLERK 24-JAN-98
...
Geoni SH_CLERK 3-FEB-00
...
Cabrio SH_CLERK 7-FEB-99
...
Bell SH_CLERK 4-FEB-96
Everett SH_CLERK 3-MAR-97
cCain SH_CLERK 1-JUL-98
...
45 rows selected
You can use
RPAD
to add characters (by default, spaces) to the end of character data.
The
LPAD
function adds characters to the beginning of character data.
In
, the result set shows a simple histogram of relative salary values.
Example 223 Padding Character Data
SELECT first_name || ' ' || last_name "Name",
RPAD(' ', salary/1000, '$') "Salary"
FROM employees;
The results of the query appear.
Name Salary
-------------------------------------- ----------------
Steven King $$$$$$$$$$$$$$$$$$$$$$$
Neena Kochhar $$$$$$$$$$$$$$$$
Lex De Haan $$$$$$$$$$$$$$$$
...
107 rows selected
You can use
SUBSTR
to extract only a substring of data, specified by the starting
character position and the total number of characters.
In
, you use
SUBSTR
to abbreviate the
first_name
value to an initial,
and strip the area code from the
phone_number
value.
Example 224 Extracting a Substring of Character Data
SELECT SUBSTR(first_name, 1, 1) || '. ' || last_name "Name",
SUBSTR(phone_number, 5, 8) "Phone"
FROM employees;
The results of the query appear.
Name Phone
---------------------------- --------
S. King 123.4567
N. Kochhar 123.4568
L. De Haan 123.4569
