TRIM(varchar(50)) not working

gagan8877 · Post by **gagan8877** » Thu Jul 05, 2007 10:00 am

I have column AddressLineOne (Nullable, Varchar(50)) coming from a Complex Flat File (EBCDIC) and then goes to the transoformer, where I check if the column is NULL or it has spaces only or 0 length. If it does it needs to be rejected, else write to seq file.

Here is the condition

IF (Len(Trim(NullToValue(AddressLineOne, ' '))) > 0

So if there is a NULL it will be converted to space and then the space will be trimmed. Even after applying this, I am getting records with spaces, Length = 40 and Not Null.

I tried the following:

1. splitting the logic into 2 variables - first doing NULLToVALUE and then TRIM in the next variable, no change.

2. changed it to double quotes: IF (Len(Trim(NullToValue(AddressLineOne, " "))) > 0, no change

3. changed it to no space in NullToValue : IF (Len(Trim(NullToValue(AddressLineOne, ''))) > 0, no change

4. changed it to IF (Len(Trim(NullToValue(AddressLineOne, ' '), ' ', 'B')) > 0, no change

5. changed it to IF (Len(Trim(AddressLineOne)) > 0, failed, bcoz NULLs cannot be trimmed. It is trimming NULL values, because I checked by inserting a new column ISNULL(AddressLineOne)

If I view the seq file in Notepad, it shows spaces, if I open in Notepad++ (another free tool) it shows: NUL NUL NUL NUL..........length 40

Could it be a different character than a space or is there anything I am doing wrong? If it is a different character, how I find what is the character?

Please help.

ashwin141 · Post by **ashwin141** » Thu Jul 05, 2007 10:05 am

Why you are using TRIM function on a Varchar field - do you think it's needed?

Ash

gagan8877 · Post by **gagan8877** » Thu Jul 05, 2007 10:15 am

ashwin141 wrote:Why you are using TRIM function on a Varchar field - do you think it's needed?

Ash

Hi Ash

I am using TRIM because I found spaces in the target sequential file and I wanted to reject those rows.

ashwin141 · Post by **ashwin141** » Thu Jul 05, 2007 10:19 am

Gagan,

You are writing to a Varchar field so there cannot be any spaces unless you write it - please have a look at what you are doing in this condition

IF (Len(Trim(NullToValue(AddressLineOne, ''))) > 0

Why you are assigning spaces to null value and then trying to TRIM it, doesn't make any sense.

Cheers!
Ash

Ash[/quote]

Hi Ash

I am using TRIM because I found spaces in the target sequential file and I wanted to reject those rows.[/quote]

gagan8877 · Post by **gagan8877** » Thu Jul 05, 2007 10:21 am

Hi Ash

The reason of doing that is because if it encounters a NULL, the TRIM fails. So if there is a NULL, I convert it to space and then trim it.

ashwin141 · Post by **ashwin141** » Thu Jul 05, 2007 10:26 am

Hey Gagan,

Please just try this...

IF (Len((NullToValue(AddressLineOne, ''))) > 0

Use an empty string ('') rather than spaces - and no TRIM is needed here,
Source field AddressLineOne is Varchar - I assume.

Cheers!
Ash

gagan8877 · Post by **gagan8877** » Thu Jul 05, 2007 10:37 am

Ash

I can definitely try that but here is a smple of the output:

AddressLineOne, Length
"16 PENNY LANE","13"
"4-310 13TH AVE S","16"
" ","40"
" ","40"
" ","40"
" ","40"
" ","40"
" ","40"
" ","40"

but wihout using TRIM, how can we remove the ones with spaces?

gagan8877 · Post by **gagan8877** » Thu Jul 05, 2007 10:47 am

Tried without the TRIM, no change.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 05, 2007 11:48 am

Code: Select all

If Len(Convert(" ", "", NullToValue(InLink.TheColumn, " "))) > 0 ...

gagan8877 · Post by **gagan8877** » Thu Jul 05, 2007 12:06 pm

ray.wurlod wrote:
Code: Select all
If Len(Convert(" ", "", NullToValue(InLink.TheColumn, " "))) > 0 ...

Hi Ray

Thanks for the reply. I tried that, no luck yet

ray.wurlod · Post by **ray.wurlod** » Thu Jul 05, 2007 1:03 pm

If this is in a constraint expression, lose the IF

Code: Select all

Len(Convert(" ", "", NullToValue(InLink.TheColumn, " "))) > 0

will suffice as a constraint expression

us1aslam1us · Post by **us1aslam1us** » Thu Jul 05, 2007 1:13 pm

I guess Ray's logic should work fine unless there is something wierd but it will also removes the spaces from valid fields too. But here are some suggestions:
1. Open your file in any hex editor and confirm whether it is space or any other character.
2. Is there any default value taken by NulltoValue function.
3. Try using len(nulltovalue(Addresslineone,''))>0 with len(trim(Adresslineone))>0 although the latter is not needed for varchar fields.

IHTH

ray.wurlod · Post by **ray.wurlod** » Thu Jul 05, 2007 1:43 pm

The point is that it doesn't matter - in a constraint expression - whether anything is removed - the result does not propagate. The expression will return True if there is any non-space character in the string, and False otherwise.

gagan8877 · Post by **gagan8877** » Fri Jul 06, 2007 10:24 am

ray.wurlod wrote:The point is that it doesn't matter - in a constraint expression - whether anything is removed - the result does not propagate. The expression will return True if there is any non-space character in the string, and False otherwise.

Thanks to all. I tried all the suggestions, but no success. I found something wierd though. In the job design. The data is read from 4 complex flat files and then joined using a left outer join and the cff which has the field is intermidiate link in the join. If I put a transformer before the join, the constraint

Len(Trim(NullToValue(DSLink71.EEADD1, ''))) > 0

works as expected, but if I put it after the join, it does not. The join is based on different key columns and the metadata of the field, that needs to be checked for spaces remains unaltered and simply new columns are added to the flow.

I tried sorting the input to the Transformer from Auto to Hash and the key as the field "adresslineone", but did not work. So I think there is no other character except space.

Then I tried running the Transformer in Sequential mode with Ordered and the key as "addressLineOne", No change.

But I tried converting the value to Hex 16 in Notepad++ here is what I get:

000000001 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |

---------------

Also Len(Convert(" ", "", NullToValue(DSLink71.EEADD1, " "))) > 0 does not work after the join and the value:

000000001 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |

is the Hex16 equivalent of space that I see in the target file.

Also tried

Len(Convert(" ", "", NullToValue(CompactWhiteSpace(DSLink71.EEADD1), " "))) > 0

And then sorted the output

In addition to the above mentioned Hex16 equivalent (in the begining of the file after the sort) , the file contains records in the end after the actual data and it looks like this:

00001090F 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |

So there may be more than one kind of spaces.......not sure...confused.

Then I tried the following, just thought there is no harm in trying:

Len(TRIM(CompactWhiteSpace(DSLink71.EEADD1))) > 0

Then tried

DSLink71.EEADD1 <> SPACE(40)

no change, so it join is adding something wierd, which is not space?

Help will be appreciated.

Thanks

ray.wurlod · Post by **ray.wurlod** » Fri Jul 06, 2007 11:50 am

Search the forum, on-line help or the manuals for APT_STRING_PADCHAR environment variable.

DSXchange