|
The LPAD or Left Pad function returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.
Both char1 and char2 can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as char1.
The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.
Syntax
>──LPAD──(char1 , n ─┬─────────┬─)──><
└─, char2─┘
PL/SQL Example
Display the number padded left with zeros to a length of 10:
LPAD ('55', 10, '0') ==> '0000000055'
Display the number padded left with zeros to a length of 5:
LPAD ('12345678', 5, '0') ==> '12345'
LPAD interprets its padded_length as the maximum length of the string that it may return. As a result, it counts padded_length number of characters from the left (start of the string) and then simply returns that substring of the incoming value.
Place the phrase "sell!" in front of the names of selected stocks, up to a string length of 45:
LPAD ('HITOP TIES', 45, 'sell!')
==>
'sell!sell!sell!sell!sell!sell!sell!HITOP TIES'
Because the length of "HITOP TIES" is 10 and the length of "sell!" is 5, there is room for seven repetitions of the pad string. LPAD does, in fact, generate a repetition of the pattern specified in the pad string.
Place the phrase "sell!" in front of the names of selected stocks, up to a string length of 43.
LPAD ('HITOP TIES', 43, 'sell!')
==>
'sell!sell!sell!sell!sell!sell!selHITOP TIES'
Because the length of "HITOP TIES" is 10 and the length of "sell!" is 5, there is no longer room for seven full
repetitions of the pad string. As a result, the seventh repetition (counting from the left) of "sell!" lost its last
two characters. So you can see that LPAD does not pad by adding to the left of the original string until it runs out
of room. Instead, it figures out how many characters it must pad by to reach the total, then constructs that full padded
fragment, and finally appends the original string to this fragment.
Place three repetitions of the string "DRAFT-ONLY" in front of the article's title. Put two spaces between each repetition.
LPAD ('Why I Love PL/SQL', 53, 'DRAFT-ONLY ');
==>
'DRAFT-ONLY DRAFT-ONLY DRAFT-ONLY Why I Love PL/SQL'
You can specify any number of characters to be padded in front of the incoming string value. The 53 that is hardcoded in that call to LPAD is the result of some hard calculations: the title is 17 characters and the prefix, including spaces, is 12 characters. As a result, you need to make sure that you pad to a length of at least 17 + 12*3 = 53. This is not a very desirable way to solve the problem, however; once again we have assumed that in the program we have access to all these specific values and can precompute the length we need.
SQL Example
The following example left-pads a string with the characters "*.":
SELECT LPAD('Page 1',15,'*.') "LPAD example"
FROM DUAL;
LPAD example
---------------
*.*.*.*.*Page 1
|