Trim function

lsnovick · Post by **lsnovick** » Tue Jul 17, 2007 6:42 am

I have a field that I am bringing in from a flat file. I am using the TrimB and TrimF function but when I do a length in the database, it includes a place for the space/blank. How can I make it only include real data and not spaces?

chulett · Post by **chulett** » Tue Jul 17, 2007 7:05 am

What database and how is it defined there?

kumar_s · Post by **kumar_s** » Tue Jul 17, 2007 7:14 am

What datatype it is?

balajisr · Post by **balajisr** » Tue Jul 17, 2007 7:43 am

View the file in the text editor and check whether you have any special characters other than space.

lsnovick · Post by **lsnovick** » Tue Jul 17, 2007 7:54 am

It is Varchar. I have tried all the TRIM options without it working.

kumar_s · Post by **kumar_s** » Tue Jul 17, 2007 8:17 am

How about giving some required details like source field and source system.

lsnovick · Post by **lsnovick** » Tue Jul 17, 2007 8:27 am

Source field: Varchar. Target field: Varchar. Moving csv UNIX file to Oracle database.

kumar_s · Post by **kumar_s** » Tue Jul 17, 2007 8:47 am

Just check if there is any defaults value been inserted in during target stage. What is the job design. What is the stage right after transformer(where Trim function is used)?

TJX Companies · Post by **TJX Companies** » Tue Jul 17, 2007 8:48 am

I had the same problem in one of my jobs. I resolved it initially in a roundabout way by calculating the length by using Index function on first space to get the length of the string and then moving (1, length) to the output field.

Later I found that if you define your field as VarChar in all the stages TRIM works properly. In one of my stages inbetween I had the field definition of Char that cuased the issue. Please check whether you definied the field as VarChar in all stages. If you still have the issue try using my first roundabout method. Good Luck.

Aruna.

thamark · Post by **thamark** » Tue Jul 17, 2007 11:32 am

lsnovick wrote:I have a field that I am bringing in from a flat file. I am using the TrimB and TrimF function but when I do a length in the database, it includes a place for the space/blank. How can I make it only include real data and not spaces?

Check the flat file to see, if you are getting spaces or un printable characters. Remeber TRIM will not consider these un printable characters for trimming.

You could check this by opening the file in unix, which will not show complete line or you can download the file to your local machine and open the file in texpad to see, if you have these characters in it...

DSXchange

Trim function

Trim function

Re: Trim function