I am trying write a SQL SELECT statement in Snowflake where I need to select a column 'xyz' form table 'a'(select xyz from a). Although, the number in column xyz is formatted into 6,7,8 digits and I want to convert that in select statement itself to 16 digits with leading 0's. I used concat() but since the column contains either 6,7,8 digits I am not able to format it into max 16 digits with leading 0's.

Note: I need it to be in select statement as I cant update the column in the database to 16 digit format.

Example

Input: 123456, 1234567, 12345678
Output should be: 0000000000123456, 0000000001234567, 0000000012345678

Can someone help me out here please. Thanks!

CodePudding user response：

Using

CodePudding user response：

If you want dynamic width, or to stick to a form of CONCAT then here are some extra ways.

SELECT column1,
    TO_CHAR(column1, '0000000000000000') as fixed_width,  
    TO_CHAR(column1, REPEAT('0', 16)) as dynamic_width,
    RIGHT(REPEAT('0', 16) || column1::text, 16) as another_way,
    LPAD(column1, 16, '0')
FROM VALUES 
    (123456), 
    (1234567),
    (12345678),
    (12345.678),
    (1234567890123456789)

the two TO_CHAR/TO_VARCHAR methods don't deal with floating results, where-as the RIGHT version does. But that doesn't handle values that are larger the 16 DP

CodePudding user response：

with data(c) as (
    select * FROM (
        values (12345), (293848238), (284), (239432043223432)
    )
)
select lpad(c, 16, '0') as c from data;

 ------------------ 
| C                |
|------------------|
| 0000000000012345 |
| 0000000293848238 |
| 0000000000000284 |
| 0239432043223432 |
 ------------------