469,360 Members | 1,782 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,360 developers. It's quick & easy.

Leading Zeros

On a z/OS DB2 v8 platform, this refers to formatting a SMALLINT field as
two zoned-decimal (character) numerics, with a leading zero when there
is only one significant digit. I tried several different CAST formats
with no luck.

Is this possible?

Thanks,

Walter Rue
Oct 1 '08 #1
4 11982
On Oct 1, 3:16*am, WalterR <ws...@verizon.netwrote:
On a z/OS DB2 v8 platform, this refers to formatting a SMALLINT field as
two zoned-decimal (character) numerics, with a leading zero when there
is only one significant digit. *I tried several different CAST formats
with no luck.
Don't know if it works on your platform, nor if it fits you needs,
but ...

db2 "with T(n) as (values 11,2,63,4) select case when n < 10 then '0'
else '' end || char(n) from T"

/Lennart

Oct 1 '08 #2
Or, you can use RIGHT and DIGITS to do the same:

with o(n) as (values 11,2,63,4),
t(n) as (select cast(n as smallint) from o)
select right(digits(n),2) from t

-Chris
Oct 1 '08 #3
On Wed, 01 Oct 2008 02:16:36 +0100, WalterR <ws***@verizon.netwrote:
On a z/OS DB2 v8 platform, this refers to formatting a SMALLINT field as
two zoned-decimal (character) numerics, with a leading zero when there
is only one significant digit. I tried several different CAST formats
with no luck.

Is this possible?

Thanks,

Walter Rue
SELECT RIGHT(DIGITS(CAST(1 AS SMALLINT)), 2) AS I
FROM SYSIBM.SYSDUMMY1

UNION ALL

SELECT RIGHT(DIGITS(CAST(4 AS SMALLINT)), 2) AS I
FROM SYSIBM.SYSDUMMY1

UNION ALL

SELECT RIGHT(DIGITS(CAST(16 AS SMALLINT)), 2) AS I
FROM SYSIBM.SYSDUMMY1;

I
-----
01
04
16
Cheers,

Dave.
Oct 1 '08 #4
Dave Hughes wrote:
On Wed, 01 Oct 2008 02:16:36 +0100, WalterR <ws***@verizon.netwrote:
>On a z/OS DB2 v8 platform, this refers to formatting a SMALLINT field
as two zoned-decimal (character) numerics, with a leading zero when
there is only one significant digit. I tried several different CAST
formats with no luck.

Is this possible?

Thanks,

Walter Rue


SELECT RIGHT(DIGITS(CAST(1 AS SMALLINT)), 2) AS I
FROM SYSIBM.SYSDUMMY1

UNION ALL

SELECT RIGHT(DIGITS(CAST(4 AS SMALLINT)), 2) AS I
FROM SYSIBM.SYSDUMMY1

UNION ALL

SELECT RIGHT(DIGITS(CAST(16 AS SMALLINT)), 2) AS I
FROM SYSIBM.SYSDUMMY1;

I
-----
01
04
16
Cheers,

Dave.
I had been confidently looking for something built-in, but I guess I was
assuming it would have a more obvious name. Anyway, DIGITS is it.
Thanks also for the CASE solution. I tried both.

Walter Rue
Oct 4 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by samik_tanik | last post: by
1 post views Thread by mmmgood1 | last post: by
6 posts views Thread by Clint Stowers | last post: by
5 posts views Thread by OneDay | last post: by
6 posts views Thread by Rich Raffenetti | last post: by
6 posts views Thread by JimmyKoolPantz | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.