| René Nyffenegger's collection of things on the web | |
|
René Nyffenegger on Oracle - Most wanted - Feedback
|
RPAD and LPAD [Oracle SQL] | ||
lpad ('string', n [, 'string_pad')
rpad ('string', n [, 'string_pad')
string is left padded to length n with string_pad. If string_pad is ommited, a space will be used as default
rpad is similar, but pads right instead of left.
Demonstration 1
The second parameter (n) to lpad or rpad determines the resulting length. If string is longer than n, string will be cut:
select
lpad('string with 25 characters', 20) lpad,
rpad('string with 25 characters', 20) rpad
from dual;
LPAD RPAD -------------------- -------------------- string with 25 chara string with 25 chara Demonstration 2
The literal string is lpadded to all lengths between 1 and 15. The < shows where the string is cut. As soon as the second parameter
becomes larger than the length of the first parameter, lpadding occurs. The default is space, so spaces are prepended to string:
begin
for i in 1 .. 15 loop
dbms_output.put_line(
lpad('string', i) || '<'
);
end loop;
end;
/
s<
st<
str<
stri<
strin<
string<
string<
string<
string<
string<
string<
string<
string<
string<
string<
Demonstration 3
Same thing as in demonstration 2, but with
rpad instead of lpad:
begin
for i in 1 .. 15 loop
dbms_output.put_line(
rpad('string', i) || '<'
);
end loop;
end;
/
s< st< str< stri< strin< string< string < string < string < string < string < string < string < string < string < Demonstration 4
This demonstration is again similar to demonstration 2, but it uses a non-default padding string (
.,_).
begin
for i in 1 .. 15 loop
dbms_output.put_line(
lpad('string', i, '.,_')
);
end loop;
end;
/
s st str stri strin string .string .,string .,_string .,_.string .,_.,string .,_.,_string .,_.,_.string .,_.,_.,string .,_.,_.,_string Demonstration 5
This demonstration shows how to pretty print some output.
create table rlpad_tbl ( col_1 varchar2(10), col_2 varchar2(10) ); insert into rlpad_tbl values ('apple', 'pear' ); insert into rlpad_tbl values ('grapefruit','mango' ); insert into rlpad_tbl values ('orange', 'banana' ); insert into rlpad_tbl values ('lemon', 'watermelon');
Now, the values of col_1 and col_2 are selected and concatenated:
select col_1 || ' - ' || col_2 from rlpad_tbl;
This results in:
apple - pear grapefruit - mango orange - banana lemon - watermelon
The length of the values of col_1 varies.
This is because col_1 is of type varchar2. Only the relevant (or used) portion of its capacity is printed.
It might be desired to neatly align the parts of the concatenated string. rpad and lpad comes to rescue:
select rpad(col_1, 10) || ' - ' || lpad(col_2, 10) from rlpad_tbl; apple - pear grapefruit - mango orange - banana lemon - watermelon Empty string side effects
Oracle treats an empty string as null. And
nulls are always a bit special within Oracle. Particularly,
rpad('', n) and lpad('', n) returns null.
|