PostgreSQL LPAD() String Function

PostgreSQL LPAD() String Function

The PostgreSQL LPAD() function is used to pad the left side of a string with a specified set of characters to a specified length. This function is essential for formatting text data to ensure consistent length and alignment.

---

Syntax

LPAD(string, length, fill)

The LPAD() function has the following components:

• string: The string to be padded.
• length: The length of the resulting string after padding.
• fill: The set of characters to pad the string with.

---

Example PostgreSQL LPAD() Queries

Let's look at some examples of PostgreSQL LPAD() function queries:

1. Basic LPAD() Example

SELECT LPAD('Hello', 10, '*') AS padded_string;

This query pads the string 'Hello' with asterisks on the left to make a total length of 10 characters, resulting in '*****Hello'.

2. LPAD() with Different Fill Characters

SELECT LPAD('PostgreSQL', 15, '-') AS padded_string;

This query pads the string 'PostgreSQL' with hyphens on the left to make a total length of 15 characters, resulting in '-----PostgreSQL'.

3. LPAD() with Column Values

SELECT id, name, LPAD(name, 10, ' ') AS padded_name
FROM users;

This query retrieves the id, name, and the left-padded version of the name for each row in the users table, padding the name with spaces to a total length of 10 characters.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the LPAD() function to format text data.

Step 1: Creating a Table

This step involves creating a new table named users to store user data.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

In this example, we create a table named users with columns for id and name.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the users table.

INSERT INTO users (name)
VALUES ('Alice'),
       ('Bob'),
       ('Charlie');

Here, we insert data into the users table.

Step 3: Using the LPAD() Function

This step involves using the LPAD() function to format the text data in the users table.

Basic LPAD()

SELECT LPAD('Hello', 10, '*') AS padded_string;

This query left-pads the string 'Hello' with asterisks to a total length of 10 characters.

LPAD() with Different Fill Characters

SELECT LPAD('PostgreSQL', 15, '-') AS padded_string;

This query left-pads the string 'PostgreSQL' with hyphens to a total length of 15 characters.

LPAD() with Column Values

SELECT id, name, LPAD(name, 10, ' ') AS padded_name
FROM users;

This query left-pads the values in the 'name' column of the 'users' table with spaces to a total length of 10 characters.

These queries demonstrate how to use the LPAD() function to format the text data in the users table, including basic usage and handling different fill characters.

Conclusion

The PostgreSQL LPAD() function is a fundamental tool for formatting text data by padding the left side of a string to a specified length. Understanding how to use the LPAD() function and its syntax is essential for effective text data manipulation in PostgreSQL databases.