Padding and trimming

A couple of very popular PL/SQL functions for strings are the ones used for padding and trimming characters. Their standard usage is for providing a simple way of formatting data to be printed in columns, so the default character is blank (' ').

But let's think to a situation where make sense using a different character for padding, for instance zero ('0'). It could happen that we have a table with spare parts descriptions, described by a seven digit code. We want to merge this table with another one, where the codes are ten digit wide, so we pad our original codes with zero. Question is: leading or trailing?

We could do both. LPAD() is for padding on the left, and RPAD on the right. At the same way we could need to remove extra characters on both side of a string. For this purpose we could use LTRIM() or RTRIM() or the ISO SQL TRIM() function, that could even trim on both sides in one single shot:

declare
    l_code1 char(7) := '5467312';
    l_code2 char(7) := '0745667';
    l_new_code1 char(10);
    l_new_code2 char(10);
begin
    l_new_code1 := lpad(l_code1, 10, '0');
    l_new_code2 := rpad(l_code2, 10, '0');
    
    dbms_output.put_line(l_new_code1);
    dbms_output.put_line(l_new_code2);

    dbms_output.put_line(ltrim(l_new_code1, '0'));
    dbms_output.put_line(rtrim(l_new_code2, '0'));

    dbms_output.put_line(trim(both '0' from l_new_code2));
    dbms_output.put_line(trim(leading '0' from l_new_code2));
    dbms_output.put_line(trim(trailing '0' from l_new_code2));  
end;

Chapter 8 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about strings datatypes and related functions.