メインコンテンツにスキップ

PostgreSQL LPAD

The PostgreSQL `LPAD` function is a string function used to pad the left side of a string with a specific set of characters until the string reaches a specified length. It is useful for formatting strings to ensure consistent output lengths.

Usage

The `LPAD` function is used to format or align strings by prepending characters to ensure a uniform length. It is commonly used in formatting reports or aligning data.

LPAD(string, length, fill_string)
  • string: The original string to be padded.
  • length: The desired total length of the resulting string after padding. If this length is less than the length of `string`, the `string` will be truncated to the specified `length`.
  • fill_string: The string used for padding. If omitted, spaces are used by default. If `fill_string` is longer than needed for padding, it will be truncated to fit the required padding length.

Examples

1. Basic Padding with Spaces

SELECT LPAD('123', 5, ' ');

This example pads the string '123' with spaces to reach a total length of 5, resulting in ' 123'.

2. Padding with Custom Characters

SELECT LPAD('abc', 6, '0');

Here, 'abc' is padded with zeros to make it 6 characters long, resulting in '000abc'.

3. Complex Padding with Multi-Character String

SELECT LPAD('post', 10, '-=+');

The string 'post' is padded with the sequence '-=+' to reach a length of 10, producing '-=+-=+post'.

Tips and Best Practices

  • Choose appropriate padding characters. Use padding characters that make sense for your data context and readability.
  • Be mindful of the resulting length. Ensure that the specified length is greater than or equal to the original string length to avoid truncation.
  • Use consistent lengths for uniformity. When formatting tables or reports, maintain consistent padding lengths for alignment.
  • Test with different fill strings. Test your `LPAD` implementations with various `fill_string` inputs to ensure they behave as expected.
  • Consider character sets and encodings. When working with non-standard character sets or encodings, ensure that the `LPAD` function behaves as expected for internationalization purposes.