Trim Functions

JDionne · Post by **JDionne** » Thu Oct 02, 2003 2:24 pm

I am trying to use the trim function to help identify a blank string field and then with a If then Else statment trun it into a Null
This is my code

If Trim(Transform_Full_File.FNAME, "R") = '' Then @Null Else Transform_Full_File.FNAME

Unfortuanatly it does not work, though it makes all the sence in the world to me.
Any Thoughts?
Jim

datastagedummy · Post by **datastagedummy** » Thu Oct 02, 2003 2:32 pm

Try using

If Len(Trim(Transform_Full_File.FNAME)) = 0 Then @Null Else Transform_Full_File.FNAME

JDionne · Post by **JDionne** » Thu Oct 02, 2003 2:51 pm

quote:Originally posted by datastagedummy
[br]Try using

If Len(Trim(Transform_Full_File.FNAME)) = 0 Then @Null Else Transform_Full_File.FNAME

I got the same result using that bit of logic...any other thoughts?

inter5566 · Post by **inter5566** » Thu Oct 02, 2003 3:11 pm

Jim,

When you specify the R option with the trim statement, you need to pick a value you want to -R-emove. Perhaps DS assumed that you wanted to remove nulls. Either way, I believe that the R option will never remove all characters in the string, it reduces to one occurance. I have successfully used trim(link.fieldname)="" I don't know if the double quotes are required or if singles will work. Also use caution with the @null system variable, I recall having strange things happen when using it.

Hope this helps,

Steve

JDionne · Post by **JDionne** » Thu Oct 02, 2003 3:22 pm

quote:Originally posted by inter5566
[br]Jim,

When you specify the R option with the trim statement, you need to pick a value you want to -R-emove. Perhaps DS assumed that you wanted to remove nulls. Either way, I believe that the R option will never remove all characters in the string, it reduces to one occurance. I have successfully used trim(link.fieldname)="" I don't know if the double quotes are required or if singles will work. Also use caution with the @null system variable, I recall having strange things happen when using it.

Hope this helps,

Steve

Steve,
That worked like a charm!!!!
Thanx Man
Jim

ray.wurlod · Post by **ray.wurlod** » Thu Oct 02, 2003 5:47 pm

The second argument to TRIM is the character to remove. You were asking to remove leading, trailing and multiple "R" characters! D'oh!

Good that you have something working. I usually prefer to check for single space character as well, for example:
If Trim(link.column) &GT; " " Then link.column Else @NULL

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

xli · Post by **xli** » Thu Oct 02, 2003 10:21 pm

If you just want to identify a blank string containing one or more space, there is a built-in tranform function StringIsSpace under Tranformssdk directory in DataStage Manager. If you cannot find it, the basic code to check a blank string is like :

If Not(IsNull(Transform_Full_File.FNAME) or Transform_Full_File.FNAME = "") and len(Trim(Transform_Full_File.FNAME, " ", "A")) = 0
then @NULL
else Transform_Full_File.FNAME

xiong