Trim Issues while sending Data to Fixed length File

[kbsuryadev]

The input Data comes as varchar(10) - TERADATA (SOURCE)
col
0000
0009508
03669
08018
08065
0003065
09620


desired output ( i used char(5)) FIXED LENGTH FILE(TARGET)
00000
09508
03669
08018
08065
03065
09620


I have tried this code
if (DSLink3.col) ='0000' Then '00000' else DSLink3.col[3,7]

When the job ran the output is as follows
08018 output comes as 018 (it should be 08018)
09620 ------- 620
08065 ------- 065
0003065 ----- 065


Any inputs on this issue is greatly appreciated.


Thank you all.

[chulett]

You should take the right-most 5 characters and then pad on any additional leading zeroes if the original length was less than 5. Are you certain that your 10 digit source will always fit in 5 digits without loss of significant digits?

[ray.wurlod]

Code: Select all

Right(("00000" : InLink.TheValue), 5)

The inner set of parentheses is not actually necessary; I included it for clarity.

[chulett]

Nicely done, Sir. Forgot that you don't even need to check, just slap some on and hack away.

[kbsuryadev]

I used EDIT METADATA PROPERTIES

%05d (float type) and field width =5 (float type)
column metadata properties.

Output SQL type is smallint