Len function not working properly

gaurang033 · Post by **gaurang033** » Thu Aug 30, 2012 2:16 am

Hi all,

I have following job design

FF ---> Transformer ---> Transformer2 ---> FF2

FF - Fixed Length, All columns are characters
Transformer -> converts Characters to Varchar

Transformer2--> Uses Stage variable (svLength)

svLength=Len(Trim(Input.Col1[1,8]))

However svLength always gives me 8, even for empty strings.

Any solutions

ShaneMuir · Post by **ShaneMuir** » Thu Aug 30, 2012 2:21 am

Well obviously your string is not empty.

If it was originally a char field, it is possible that it has been padded. Though I would have thought that if the input is fixed that it should be spaces.
What is your default pad string? Is it 0x0?

gaurang033 · Post by **gaurang033** » Thu Aug 30, 2012 2:25 am

Any idea how to remove this pad string. ??

ArndW · Post by **ArndW** » Thu Aug 30, 2012 2:31 am

No, not before we know what the padded values are.

How are you converting from CHAR to VARCHAR in the transform stage?

You can use the STR() function to see what a given character is, so in your case make a debug output from your transform, output STR(In.Col[8]) and see if it is indeed 0x0 or some other value.

ShaneMuir · Post by **ShaneMuir** » Thu Aug 30, 2012 2:33 am

Actually reviewing your original post - I would have assumed that the input should have been padded with spaces from the fixed width file.

Do you know what your current pad string is?

Anyway - there is a environment variable APT_STRING_PADCHAR or something similar. Here you can set the value you wish to use to pad your values.

gaurang033 · Post by **gaurang033** » Thu Aug 30, 2012 2:43 am

Hi All,

Thanks for your replies,

my pad string is 0x20. If I am not wrong this means space.

I changed my trim function as follows and with surprise it worked.

Code: Select all

 Trim(input.col1,char(0),'')

Can anyone explain me why ???

ArndW · Post by **ArndW** » Thu Aug 30, 2012 3:43 am

Have you checked your source to see which character is present there, if any? If it were 0x0 then no padding would be performed .

ankursaxena.2003 · Post by **ankursaxena.2003** » Thu Aug 30, 2012 12:13 pm

If you are sure that it is space then you can use Convert function in transformer to convert space to nothing.

I don't remember the syntax. But it is somewhat like below:

Code: Select all

Convert(Old String, New String, Column Name)

The above function can be wrong. But, I am sure there is Convert function in transformer