Introduction and usage of left filling (lpad) and right filling (rpad) in Oracle

Time:2019-10-7

Preface

Fixed field length is often used in practical applications, but some data can not reach the prescribed length, which requires us to fill in with other characters. There are functions in Oracle to solve this problem. Let’s see the details below.

grammar

  • LPAD(string,padded_length,[ pad_string])Fill from left, L: left, PAD: pad
  • RPAD(string,padded_length,[ pad_string])Fill from right, R: right, PAD: pad

explain

  • String: the original data, that is, the data to be filled in;
  • Padded_length: the length after filling;
  • Pad_string: Fill in strings optionally and paste spaces if not

For instance:

Select lpad ('34', 6,'0') as format from dual

Implementation result 000034

Usually it can be used with the date sequence as the primary key

for example

Select to_char (sysdate,'YYYYMMDHh24miss') | | lpad ('34', 6,'0') as format from dual

Implementation results 20170614110557000034

You can replace `34’with a sequence.

for example

Select to_char (sysdate,'YYYYMMDHh24miss') | | lpad (test Sequences. nextval, 6,'0') as format from dual

summary

The above is the whole content of this article. I hope that the content of this article can bring some help to everyone’s study or work. If there is doubt, you can leave a message to exchange, thank you for your support for developpaer.