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

Tools
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.