Quick Answer

Use the following query

SELECT LPAD(column_name, desired_length, '0') as padded_column
FROM table_name;

If you have some time you can read this article with a more detailed explanation for using this and another function.

Introduction

One of the common tasks in data manipulation is formatting numbers to a specific format. In this article, we will go through the steps on how to add leading zeros to a number in Snowflake.

Step 0. Creating The Dataset

We are going to proceed to use the following dataset.

// Creating Sales table
CREATE TABLE TEST.TEST.SALES (
    ID VARCHAR(255),
    PRODUCT_NAME VARCHAR(255) NOT NULL,
    CUSTOMER_NAME VARCHAR(255) NOT NULL,
    SALES_DATE DATE NOT NULL,
    QUANTITY INT NOT NULL,
    PRICE DECIMAL(10, 2) NOT NULL
);

INSERT INTO TEST.TEST.SALES (ID,PRODUCT_NAME, CUSTOMER_NAME, SALES_DATE, QUANTITY, PRICE)
VALUES 
('1','Apple iPhone X', 'John Doe', '2022-01-01', 2, 999.99),
('2','Samsung Galaxy S21', 'Jane Doe', '2022-01-02', 1, 899.99),
('3','Apple MacBook Pro', 'John Doe', '2022-01-03', 1, 1999.99),
('4','Dell XPS 13', 'Jane Doe', '2022-01-04', 2, 1499.99),
('5','Microsoft Surface Pro 7', 'John Doe', '2022-01-05', 1, 799.99);

Method 1: Using the LPAD Function

The LPAD function in Snowflake is used to pad a string on the left with a specified character. To add leading zeros, you can use the LPAD function with the number of characters you want the column to have, and a zero as the padding character.

SELECT LPAD(column_name, desired_length, '0') as padded_column
FROM table_name;

for example in our dataset if we want to change the format of the ID, to be something like ‘000001’ instead of ‘1’ we should run the following query.

SELECT LPAD(ID , 6, '0') as padded_column
FROM SALES;

This will return us the ID column padded with 5 zeroes on the left.

Method 2: Using the TO_VARCHAR function

TO_VARCHAR is an Snowflake function that converts a expression to a VARCHAR data type, For this case we are going to use this function to change column format to a different one.

SELECT TO_VARCHAR( <numeric_expr> [, '<desired_format>' ] )
FROM table_name;

For example, when converting ID column, if we wish to change the format to be something like ‘000001’ instead of ‘1’ we should run the following query.

SELECT to_varchar(CAST(ID AS INT) ,'000000') as padded_column
FROM TEST.TEST.SALES;

On this query we are converting ID to INT as we need it to be on a format as INT where we could make the change to varchar format.

The only difference here is that if you want to use a different format that does not include the same character repeted multiple times, this function will be the answer to that.

SELECT LTRIM(to_varchar(CAST(ID AS INT) ,'$000000')) as padded_column
      ,to_varchar(CAST(ID AS INT) ,'$000000') as padded_column_wo_ltrim
FROM TEST.TEST.SALES;

Here, we are going to do the same than before but add a ‘$’ sign, with to_varchar function , also we are adding a LTRIM to get rid of an empty space that appears when calling the funcion without LTRIM.

With this function we can use more complex string formats than with LPAD

Conclusion

Adding leading zeros in Snowflake can be done using LPAD and TO_VARCHAR functions and specifying a desired format. This function can be used in SELECT and INSERT statements to format numeric data and add leading zeros. This makes your numeric data more readable and organized.