Amazon Redshift Pad Zeros – LPAD and RPAD Examples

  • Post author:
  • Post last modified:April 3, 2023
  • Post category:Redshift
  • Reading time:10 mins read

The Redshift pad functions are string functions used to remove or add the string at the beginning or at the end of the expression. An expression could be a column value from your Redshift database or schema table. In this article, we will check Redshift pad zeros using built in pad functions.

Page Contents

Introduction to Redshift String Functions

One important feature of Amazon Redshift is its extensive support of string functions. These string functions allow users to manipulate and transform string data in various ways, includes type conversion, concatenation, and substring extraction. Some of the most commonly used string functions in Redshift include LEFT, RIGHT, SUBSTRING, CONCAT, and REPLACE.

The LPAD and RPAD functions are string functions which are useful to manipulate the raw string. For instance, you can use these functions to prepend characters to an input string based on a specified length.

Amazon Redshift LPAD and RPAD Functions

Redshift Pad Functions

Amazon Redshift supports many string manipulation functions. The pad functions are widely used functions in a data warehouse environment to add or remove certain characters to the string values that are received from various sources.

AWS Redshift supports two pad functions:

  • LPAD – Works on left hand-side of string value.
  • RPAD – Works on right hand-side of string value.

You can use these function in Redshift to pad zeros or any other character provided in function arguments.

Redshift LPAD function

The LPAD function prepend characters to an input string based on a specified length. You can use this function to pad string on the left of the input string. There is an optional third argument that specifies the pad character. You can provide this as ‘0’ if you want to prepend zero to a left side of the string.

Redshift LPAD Syntax

Following is the syntax of LPAD function available in Redshift:

LPAD (string1, length, [ string2 ])

where, string1 is input string. The length is an integer that defines the length of the result of the function and the string2 is optional one or more characters that are prepended to string1.

Redshift RPAD function

The RPAD function appends characters to an input string based on a specified length. You can use this function to pad string on the right side of input string. There is an optional third argument that specifies the pad character. You can provide this as ‘0’ if you want to append zero to a right side of the string.

Redshift RPAD Syntax

Following is the syntax of RPAD function available in Redshift: 

RPAD (string1, length, [ string2 ])

where, string1 is input string. The length is an integer that defines the length of the result of the function and the string2 is optional one or more characters that are appended to string1.

Amazon Redshift LPAD and RPAD Examples

Following are some of the examples with and without Redshift LPAD and RPAD functions to prepend and append zeros to the input string.

Redshift padding zero to left of the string

SELECT LPAD(a.sample, 10, 0) as row_res 
FROM (SELECT 12345 as sample) a;
  row_res
------------
 0000012345
(1 row)

Redshift pad zero to right of the string

SELECT RPAD(a.sample, 10, 0) as row_res 
FROM (SELECT '12345.' as sample) a;
  row_res
------------
 12345.0000
(1 row)

Remove leading zero – left padded zeros

SELECT CAST(TRIM(Leading '0' FROM a.sample) as integer) as row_res
FROM (SELECT '0000012345' as sample) a;
 row_res
---------
   12345
(1 row)

Redshift pad zero without using LPAD function

You can append ‘0’ to an input string without using LPAD function. You can make use of TO_CHAR function to pad zeros.

SELECT TO_CHAR(12345, 'fm0000000000');
  to_char
------------
 0000012345
(1 row)

Benefits of LPAD and RPAD functions in Redshift

LPAD and RPAD are string functions available in Amazon Redshift that can be used to pad a string with a specific character to a certain length.

The benefits of using LPAD and RPAD functions in Redshift include:

  1. Padding strings: LPAD and RPAD functions can be used to pad strings with leading or trailing characters to a specific length. This can be useful when working with data that needs to be formatted.
  2. Data consistency: By using LPAD and RPAD functions to pad strings to a specific length, you can ensure consistency in your data. This can help to avoid errors and make your data more reliable.
  3. Simplified Applications: Using LPAD and RPAD functions can simplify your applications by allowing you to perform padding within the query itself, rather than having to write additional code to perform the padding outside of the query.

Conclusion

In conclusion, the LPAD and RPAD functions in Redshift are powerful string functions for manipulating character strings in SQL queries. LPAD allows you to pad a string with a specified character on the left, while RPAD does the same on the right. Both functions are useful for formatting data and ensuring consistency in your results.

Related Articles

Hope this helps 🙂

Tags: ,