unable to pad zero on left side

[dskid]

Hello,
I am facing a strange problem when i try to concatinate Zero on left side of an integer its not takng that i dont know what the problem is can any one help me on this?
I have to check fo the len of the integer and if the lenght is 1 then i have to pad 2 zero's and if len is 2 then i have to pad 1 zero on the left side so that total lenght will be always 3.
I tried concatinating 0 and changed the data type from integer to varchar and nothing worked.one more thing is i tried concatinating numbers other than zero and its workng fine.

thanks in advance

[us1aslam1us]

Instead of concatenating,have you tried FMT function? Something like this.

Code: Select all

FMT(in.col,"3'0'L") 

[ray.wurlod]

Have you written a parallel version of the Fmt() function? If so, please post it.

Meanwhile, you could use something like converting to a string and overwriting the rightmost part of "000". But, in parallel jobs, integers do not have leading zeroes - only strings can.

[nick.bond]

Try this code

Code: Select all

Right('000' : DecimalToString(input.MyInteger,"fix_zero,suppress_zero"), 3)

Make sure the definition of you target column is Varchar or Char(3) otherwise it will be converted back to integer.

Also make sure you have "fix_zero,suppress_zero") options set for DecimalToString otherwise it won't work as expected.

[dskid]

Thanks every one who responded immediately.
I choose to go with STR function and it solved my problem.
Thank you very much once again.

[hamzaqk]

Try the string function .. something like this .. i have done it and worked for me


Str(string, number of times to be repeated)
i.e. STR('0', 3): FieldName woud pad three zeros to the left of FieldName

so if you write somethin like

STR('0', Length of Actual field - Len(Link.ActualField) : Actualfield

it will do the job .

actual field length is the one defined in the definition . exp char(30)
Len(Link.actualfield) will give you the length in the incoming columns



hope it helps

[nick.bond]

STR('0', Length of Actual field - Len(Link.ActualField) : Actualfield

Link.ActualField is an integer as dskid said so it should be converted to a string before performing string operations on it. I know that is you tried this on a decimal field you would get warning about explicit conversion taking place, and I imagine the same is true for integers.