Len function not working properly
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 26
- Joined: Mon Oct 17, 2011 6:00 am
Len function not working properly
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
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
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?
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?
Last edited by ShaneMuir on Thu Aug 30, 2012 2:30 am, edited 1 time in total.
-
- Participant
- Posts: 26
- Joined: Mon Oct 17, 2011 6:00 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.
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.
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.
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.
-
- Participant
- Posts: 26
- Joined: Mon Oct 17, 2011 6:00 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.
Can anyone explain me why ???
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),'')
-
- Participant
- Posts: 96
- Joined: Mon May 14, 2012 1:30 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:
The above function can be wrong. But, I am sure there is Convert function in transformer
I don't remember the syntax. But it is somewhat like below:
Code: Select all
Convert(Old String, New String, Column Name)