Examples of how to use the LPAD function in Oracle

Here are detailed examples of how to use the LPAD function in Oracle:

 

# Syntax

 LPAD(string, length, [pad_string])

 - string: The original string to be padded.

- length: The total length of the string after padding.

- pad_string: Optional. The string to pad the original string with. If omitted, spaces are used by default.

 # Examples

 # 1. Basic Padding with Spaces

Pad the string 'Hello' to a total length of 10 using spaces.

 SELECT LPAD('Hello', 10) AS padded_string FROM DUAL;

-- Returns '     Hello'

  

# 2. Padding with a Specific Character

Pad the string 'Hello' to a total length of 10 using the asterisk (*) character.

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

-- Returns '*Hello'

  

# 3. Padding with a Multi-Character String

Pad the string 'Hello' to a total length of 12 using the characters 'ab'.

 SELECT LPAD('Hello', 12, 'ab') AS padded_string FROM DUAL;

-- Returns 'ababababHello'

  

# 4. Truncating the Original String

If the length specified is shorter than the original string, the original string is truncated to that length.

SELECT LPAD('HelloWorld', 5) AS padded_string FROM DUAL;

-- Returns 'Hello'

  

# 5. Padding Numbers

Convert a number to a string and pad it with leading zeros to a total length of 10.

 SELECT LPAD(12345, 10, '0') AS padded_number FROM DUAL;

-- Returns '0000012345'

  

# 6. Combining with Other Functions

Pad the result of another function, such as SUBSTR.

 SELECT LPAD(SUBSTR('Oracle', 1, 3), 10, '#') AS padded_substring FROM DUAL;

-- Returns '##Ora'

  

# 7. Padding Within Table Columns

Pad values in a column of a table. Assume we have a table employees with a column employee_id:

 SELECT employee_id, LPAD(employee_id, 10, '0') AS padded_employee_id FROM employees;

 This pads each employee_id to a total length of 10 with leading zeros.

 

# Practical Use Cases

 # 1. Aligning Data in Reports

Ensure that all string values are aligned when generating reports.

 SELECT LPAD(employee_name, 20) AS employee_name FROM employees;

 This pads each employee_name to a length of 20 with spaces, aligning them in the output.

 # 2. Creating Fixed-Length Codes

Generate codes or identifiers of fixed length.

 SELECT LPAD(order_number, 8, '0') AS order_code FROM orders;

 This converts each order_number to an 8-character string with leading zeros.

 

# 3. Formatting Numbers

Format numbers with leading zeros for consistent presentation.

 SELECT LPAD(invoice_number, 10, '0') AS formatted_invoice FROM invoices;

 This pads each invoice_number to 10 digits with leading zeros.

 

# Additional Examples

 # 1. Padding with Spaces

Pad a username to a length of 15 characters.

 SELECT LPAD('john_doe', 15) AS padded_username FROM DUAL;

-- Returns '      john_doe'

 

# 2. Padding a String with a Character

Pad 'Data' with hyphens to a total length of 10.

 SELECT LPAD('Data', 10, '-') AS padded_string FROM DUAL;

-- Returns '------Data'

  

# 3. Padding a Column in a Table

Assume we have a table products with a column product_code. Pad each product code with leading zeros to a length of 6.

 SELECT product_code, LPAD(product_code, 6, '0') AS padded_product_code FROM products;

 # 4. Combining LPAD with Other Functions

Pad the result of a concatenation.

 SELECT LPAD(CONCAT('Item', '123'), 10, '*') AS padded_string FROM DUAL;

-- Returns 'Item123'

 

 

# 5. Padding with Multi-Character String

Pad 'Report' with 'xyz' to a total length of 12.

 SELECT LPAD('Report', 12, 'xyz') AS padded_string FROM DUAL;

-- Returns 'xyzxyzReport'

  

These examples illustrate how to use the LPAD function to pad strings to a desired length with various characters, ensuring consistency and alignment in data presentation.