Padding zero's before the value based on the data

[DWH-M]

HI

I have one requirement i am tring to load data into the target like:

my input data size is numeric(10), if i get the data like 25685, i need to load into the taget like 0000025685


if data is coming 5 digit , remaining preceeding zero's should come, if 2 digit data is coming 8 digit zero's should come.

please help me to know the resolution.

thanks in advance

suma

[pandujoy]

Use a len(Str,"0" with column)

[Ravi.K]

Try with below derivation.

Right('0000000000':Inputcol,10)

[pandujoy]

This is the best option :

Str("0",8-len(Column):Column

[kaps]

FMT(Column,'8"0"L')

[jwiles]

Actually, Ravi's example using Right() is more efficient in this case as you're not having to call the Len() and Str() functions each time. The end result is the same.

If you must do zero-padding several times in a transformer and process millions of rows, you can see a distinct performance difference between the two methods.

Regards,