CODEWITHSUNDEEP
snowflake-cloud-data-platform
obautista
obautista

Reputation: 3783

Convert number to varchar using to_varchar with leading zeros in Snowflake

In Snowflake there is a number column storing values like: 8,123,456. I am struggling determining how to structure select statement to return a value like: 00008123456.

In SQL SERVER you can do something like this:

right(replicate('0', 11) + convert(varchar(11), job_no), 11) AS job_no

I understand Snowflake to_varchar function can be used, but not sure how to structure like in SQL Server.

Upvotes: 2

Views: 11054

Answers (3)

FKayani
FKayani

Reputation: 1021

Give it try with:

SELECT LPAD('12234.50', 19, '0');

/* RETURNS 19 Positions including the decimal point. */
-- 0000000000012234.50

More examples & details: https://docs.snowflake.com/en/sql-reference/functions/lpad.html#examples

Upvotes: 1

Xaaza
Xaaza

Reputation: 3

a solution provided by Greg Pavlik saved me. I just switched from DB2 to Snowflake.

So, TO_VARCHAR( <numeric_expr> [, '<format>' ] ) worked.

Upvotes: 0

Greg Pavlik
Greg Pavlik

Reputation: 11086

Just add a format string on to_varchar():

select to_varchar(8123456, '00000000000');

Upvotes: 3

Related Questions