Snowflake Pad Zeros – LPAD and RPAD with Examples

  • Post author:
  • Post last modified:December 30, 2019
  • Post category:Snowflake
  • Reading time:6 mins read

Snowflake supports many useful string functions. The pad functions are most commonly used string functions in databases. The Snowflake pad functions are commonly used to pad characters such as zeros. You can use the pad functions to add or remove the characters either at the beginning or end of an expression or column values. In this article, we will Snowflake pad functions such as LPAD and RPAD with an example to pad zeros.

Snowflake Pad Zeros - LPAD and RPAD with Examples

Snowflake Pad Functions

As mentioned earlier, Snowflake supports a lot of string manipulation functions. Among them, the pad functions are widely used functions in a data warehouse environment to add or remove certain characters in the string values that are received from various sources.

Snowflake 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 functions in Snowflake to pad zeros or any other character provided in function arguments. The pad functions work on an expression or on a value from the table column.

Snowflake LPAD function

The Snowflake LPAD function left-pads a string with characters from another string, or left-pads a binary value with bytes from another binary value. 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.

Snowflake LPAD Syntax

Following is the syntax of LPAD function available in Snowflake.

LPAD (string1, length, [ pad char ])

Where, the string1 is input string. The length is an integer that defines the left pad length and the pad char is an optional characters that are prepended to string1.

Snowflake RPAD function

The Snowflake RPAD function right-pads a string with characters from another string, or right-pads a binary value with bytes from another binary value. You can use this function to pad string on the right of the input string. There is an optional third argument that specifies the pad character.

Snowflake RPAD Syntax

Following is the syntax of RPAD function available in Snowflake.

LPAD (string1, length, [ pad char ])

Where, the string1 is input string. The length is an integer that defines the right pad length and the pad char is an optional characters that are appended to string1.

Amazon Redshift LPAD and RPAD Examples

Following section demonstrates usage of LPAD and RPAD functions to prepend and append characters to the input string.

Padding the characters such as zeros is common use case. So, we will demonstrate padding zeros in string expressions.

Snowflake padding zero to right of the string

Following SQL statement formats the input string to 10 digits and append the zeros if the length is less than 10.

SELECT RPAD(a.str, 10, 0) as rpad_zeros 
FROM (SELECT '12345.0' as str) a;

+------------+
| RPAD_ZEROS |
|------------|
| 12345.0000 |
+------------+
Snowflake padding zero to left of the string

Following SQL statement formats the input string to 10 digits and prepend the zeros if the length is less than 10.

SELECT LPAD(a.str, 10, 0) as lpad_zeros
FROM (SELECT 12345 as str) a;

+------------+
| LPAD_ZEROS |
|------------|
| 0000012345 |
+------------+
Format 5 Digit Code

Following SQL query formats the code to 5 digit.

SELECT LPAD(a.str, 5, 0) as code
FROM (SELECT 345 as str) a;

+-------+
| CODE  |
|-------|
| 00345 |
+-------+

These types of number formatting is useful to handle joining conditions. For instance, you can use the formatted code in joining condition.

Related Articles,

Hope this helps 🙂

Tags: ,