unable to trim

dh_Madhu · Post by **dh_Madhu** » Fri Jul 27, 2007 8:49 pm

Hi,
A dataset is created using the $APT_STRING_PADCHAr(0x20).
when inserting to a table, the data gets inserted along with the space.
Use of trim function has not so far helped to remove the space inspite of the data type in oracle being a varchar.
Is there any other means of doing this?

Pls let me know.
Thanks in advance!!

ArndW · Post by **ArndW** » Fri Jul 27, 2007 8:53 pm

The trim function will always remove spaces from strings, so either you don't have spaces but other characters or you don't have strings - note, you cannot remove trailing spaces from a CHAR field, just from VARCHAR. What are your datatypes in the job and the table? Are you doing a TRIM() on CHAR field and later implicilty converting to VARCHAR?

dh_Madhu · Post by **dh_Madhu** » Fri Jul 27, 2007 9:04 pm

I have a dataset ->column import stage-> oracle OCI stage.
I added the transfomer stage btween the col import and oci stage and tried performing the trim.
The datatype in table is varchar.

ArndW · Post by **ArndW** » Fri Jul 27, 2007 9:06 pm

What is the datatype from your dataset schema? Add a 2nd output link to your transform going into a peek stage with your TRIM() string and add a column of LEN(trimmed-string) and see if the results are as expected.

dh_Madhu · Post by **dh_Madhu** » Fri Jul 27, 2007 9:31 pm

The data type from the dataset is char, and the results are as expected.
The trim(string) and the len(trim(string) are the same.
The actual len(string) is 9 but the data that has gone into the table is Len(15).

ArndW · Post by **ArndW** » Sat Jul 28, 2007 12:31 am

You cannot TRIM a char field. It has fixed width and will always blank pad to the fixed length. You need to declare a VARCHAR column datatype and then TRIM your original CHAR column into that.

ray.wurlod · Post by **ray.wurlod** » Sat Jul 28, 2007 3:22 am

Pedantically you can TRIM a CHAR data type, and it is immedately padded again with APT_STRING_PADCHAR (automatically, because its data type is CHAR).

The effect is the same.

dh_Madhu · Post by **dh_Madhu** » Sat Jul 28, 2007 8:54 am

That's why am using the transformer stage in between where I specify the RHS column as a varchar. Am I missing something here?

ArndW · Post by **ArndW** » Sat Jul 28, 2007 7:51 pm

This is the first mention of a varchar RHS column. If you explicitly TRIM that column and output it to a peek stage are the spaces gone? And then they re-appear when writing to your database? What database are you using?

dh_Madhu · Post by **dh_Madhu** » Mon Jul 30, 2007 2:01 am

Am using the Siebel database...

ArndW · Post by **ArndW** » Mon Jul 30, 2007 2:11 am

Siebel is the CRM system sitting on top of a database - probably DB2 or Oracle. And what happens in the PEEK stage with the trimmed field?

dh_Madhu · Post by **dh_Madhu** » Mon Jul 30, 2007 2:14 am

ya, its the oracle...and the peek stage shows trimmed fields.

ArndW · Post by **ArndW** » Mon Jul 30, 2007 2:30 am

So the RHS column is trimmed correctly, and that is what you are passing on to your output stage where the blanks are being added in again? This would only happen if you are going to a fixed width CHAR field - are you sure you aren't doing that somewhere? Are you using user-defined SQL and, if so, are you doing any conversions there?

dh_Madhu · Post by **dh_Madhu** » Mon Jul 30, 2007 7:54 pm

The target field is a varchar field but yes!

....am using a user defined Insert statement and am not trimming there. So this should be it. Lemme try it out trimming in the query as well.
Thanks.