8 Replies Latest reply on Apr 8, 2021 2:54 AM by Nico Heinze

# Find whole number and decimal and pad zeros.  Gurus, for a given whole number or decimal I need to pad '00' to right and remaining zeros to left if the length is less than 12. For example if the number is 123 then  000000012300. Similarly for decimals if i have 999.14 then 000000099914. My approach is using an IS_NUMBER function to find if it is a number but I could only get either 0 or 1 as output. LPAD or RPAD to concatenate 0's but how to find if it is a decimal or whole number or negative number? Is there any other function that makes it easy? Open for suggestions.

INPUT                         OUTPUT

123                     000000012300

999.14                000000099914

-999.1                000000099910-

• ###### 1. Re: Find whole number and decimal and pad zeros.  Hello Arya,

LPAD Adds a set of blanks or characters to the beginning of a string to set the string to a specified length.

Best regards,

Syed

• ###### 2. Re: Find whole number and decimal and pad zeros.  The issue is not about LPAD or RPAD but how to identify a number and then a decimal and then we have to pad zeros. Any idea or suggestion?

• ###### 3. Re: Find whole number and decimal and pad zeros.   assumption is that INPUT is a decimal

• ###### 4. Re: Find whole number and decimal and pad zeros.  Easy to do, but due to the negative sign following the actual number you should use two variable ports in an EXP to make life easier.

Start with a variable port v_val100 with the following expression (assuming INPUT is never NULL):

Abs( Input * 100.0)

Next a variable port v_is_neg of type Integer with this expression:

Input < 0.0

(that's a shortcut for "IIF( Input < 0.0, TRUE, FALSE)")

Now the output port:

LPad( To_Char( v_val100, 12, '0') || IIF( v_is_neg, '-', '')

Regards,

Nico

• ###### 5. Re: Find whole number and decimal and pad zeros.  Thank you Nico. I used the format multipliers in oracle to do this. But I will try to implement in Informatica and see how the  data flows. Thanks again. you rock.

• ###### 6. Re: Find whole number and decimal and pad zeros.  Hi Arya,

happy to help whenever I can. ;-)

Honestly I would definitely try the PowerCenter version as well, and then I would be interested to see which one is faster. Please let us know what you find.

Cheers,

Nico

• ###### 7. Re: Find whole number and decimal and pad zeros.  Thank you Nico. Both are effective and fast. I didn't find much difference in execution. I tested in both way. Kudos to you for bringing that up in Informatica. You are awesome.

• ###### 8. Re: Find whole number and decimal and pad zeros.  Hi Stephan,

sorry for being so pernickety, but you shouldn't use TO_INTEGER() here (this function returns an Integer which cannot hold 12 digits). You can, however, use TO_BIGINT() here:

LPad( To_Char( To_Bigint( Abs( input) * 100)), 12, '0') || IIF( Input < 0, '-', '')

The reason why I haven't used this single expression is that with two variable ports it's easier to change the data types or lengths or whatever.

Thanks and regards,

Nico