I have input data as below
I want to add leading 0s to the PRODUCT_NUMBER to make it total 10 digit , desired output should be like below
Is there a way to do this in snowflake?
2 Answers
Yes, it's possible to use TO_CHAR function to format the numbers:
select TO_CHAR( num, '0000000000' ) AS res from values
(145678),
(3456),
(12) tmp(num);
+------------+
| RES |
+------------+
| 0000145678 |
| 0000003456 |
| 0000000012 |
+------------+
https://docs.snowflake.com/en/sql-reference/functions/to_char.html
Another alternative is lpad which dynamically pads specified character until the string reaches the length N. If the length of string was > N to begin with, it will return first N characters and truncate the rest.
select lpad(col, 10, '0')
Related