SQL change number format to custom "0000000000000000" from any integer number in SELECT statement

Question

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!

Answer 1

Using TO_VARCHAR with format provided:

SELECT TO_VARCHAR(12345, '0000000000000000');

---

WITH cte(c) AS (
  SELECT * FROM (VALUES (123456), (1234567),(12345678))
)
SELECT c, TO_VARCHAR(c, '0000000000000000') AS result
FROM cte;

Output:

Answer 2

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 |
+------------------+

Answer 3

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)

COLUMN1	FIXED_WIDTH	DYNAMIC_WIDTH	ANOTHER_WAY	LPAD(COLUMN1, 16, '0')
123,456	0000000000123456	0000000000123456	000000123456.000	000000123456.000
1,234,567	0000000001234567	0000000001234567	000001234567.000	000001234567.000
12,345,678	0000000012345678	0000000012345678	000012345678.000	000012345678.000
12,345.678	0000000000012346	0000000000012346	000000012345.678	000000012345.678
1,234,567,890,123,456,789	################	################	890123456789	1234567890123456

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

Using you SQL

So using the SQL you put in your comment, and then using 2 CTE's for fake out some data AND using one of the many answers to your question, we get:

WITH "abc" as (
    SELECT * FROM VALUES
        (123456),
        (1234567)
        t(ki)
), "xyz" as (
    SELECT * FROM VALUES
        (6, 123456),
        (7, 1234567)
    t(ki_value, piv_id)
)
SELECT DISTINCT 
    y.kI, 
    yo.ki_value, 
    yo.piv_num 
FROM "abc" as y 
left join (
    select 
        ki_value, 
        LPAD(piv_id, 16, '0') as piv_num 
        --concat('0000000000',piv_id) as piv_num 
    from "xyz"
) as yo on y.KI = yo.piv_num 
WHERE y.KI in (123456,1234567)

this gives:

KI  |KI_VALUE   |PIV_NUM
--|--|--
123,456 |6  |0000000000123456
1,234,567   |7  |0000000001234567

So this "works" but as your SQL is written, it's really bad. The WHERE statement is checking the values are numbers and are in the range, why on earth are you want to string pad numbers as TEXT with however many number of zeros in front, because you should leave them as numbers.