Putting 0's in front of the number if it is less than 19 in length

Tools & Utilities
Enthusiast

Putting 0's in front of the number if it is less than 19 in length

While doing fast export, I need to convert the number to character.
But I want to pad it with 0's on left so that my number is right adjusted.

so e.g. I have numbers 9050 and 11060 then the output should be 000000000000009050 and 000000000000011060.

I am tried using CAST(CAST(member_id AS FORMAT '9(17)9') AS CHAR(18))
as well as CAST(CAST(member_id AS FORMAT 'z(17)9') AS CHAR(18))

But doesnt work.

Any other way to do it?

Thanks in advance
2 REPLIES
SN
Enthusiast

Re: Putting 0's in front of the number if it is less than 19 in length

Hi,

This should do the trick.

SEL SUBSTR('000000000000000000',1,(18-CHARACTER_LENGTH(TRIM('9050'))))||'9050';

Replace '9050' with column name and it should format all rows in the table.

reg,
Enthusiast

Re: Putting 0's in front of the number if it is less than 19 in length

Thanks.
This works.