Trim on char fields

[zulfi123786]

Hi,

I am trying to read a sequential file having 10 columns all defined as varchar.
A transformer stage follows the sequential file where i am converting some varchar to char. There is a field 'GMAB' of length 3 having value 'IN ' for all the records.

In the transformer stage i am using TrimLeadingTrailing function on the field and mapping to o/p as char(3). now when i am checking using the condition " If TrimLeadingTrailing(GMAB) = 'IN' Then 1 Else 0 " in another transformer which is the last stage of my job, is giving the value '0'.


What could be the possible reason

[chulett]

You can't trim spaces from a char. Why not simply check for = 'IN ' instead?

[zulfi123786]

You can't trim spaces from a char. Why not simply check for = 'IN ' instead? ...

i need this field to make joins, so should trim it.

[narasimha]

Is it ok if you do not do the conversion from varchar to char? Then the TrimLeadingTrailing function would work.

[chulett]

Joining to another char field of the same size? If so, then no trim needed. If something else, then please explain.

[zulfi123786]

Is it ok if you do not do the conversion from varchar to char? Then the TrimLeadingTrailing function would work.

The problem is that i need to convert it to char. so i am searching for a solution!!!

[zulfi123786]

Joining to another char field of the same size? If so, then no trim needed. If something else, then please explain. ...

I need to join this field with another field of length char(1)

[chulett]

And by "join" you mean in the database / sql sense rather than say, concatenation, yes? What are your rules for "joining" a char(1) with a char(3)?

[zulfi123786]

And by "join" you mean in the database / sql sense rather than say, concatenation, yes? What are your rules for "joining" a char(1) with a char(3)? ...

isn't is possible to join char(1) field with char(3) field???

[chulett]

All things are possible, but think about it. Out of the box the only values that may match to the char(1) are 1 character values in the char(3) field, is that what you need? Or are they a match if the first character is the same in both fields? You'd have to tell us what your match rules are.

[zulfi123786]

All things are possible, but think about it. Out of the box the only values that may match to the char(1) are 1 character values in the char(3) field, is that what you need? Or are they a match if the first character is the same in both fields? You'd have to tell us what your match rules are.

you are right. o in char(only those values which are of length 13) field have to be matched with the other char(1) field. so i want to trim the char(3) field so as to remove any leading spaces, is this possible without changing the datatype of char(3) to varchar. If so please suggest.

[chulett]

Hmmm... 13? New number here. Sure, leading spaces can be trimmed from a char field. It's the trailing ones being discussed as problematic here, the "padding" that is automatically done for the data type to its full size. Sorry if that wasn't clear.

[zulfi123786]

Hmmm... 13? New number here. Sure, leading spaces can be trimmed from a char field. It's the trailing ones being discussed as problematic here, the "padding" that is automatically done for the data type to its full size. Sorry if that wasn't clear.

sory it was 3 not 13. one more doubt!! what happen it the trailing spaces are trimmed from a char field and mapped to a field of same length .
Ex: if ID='IN ' now if i trim it would become 'IN' and if i map this to a field char(3) would it again pad a space???

[ray.wurlod]

It will pad, and it will use whatever character is set as the value of APT_STRING_PADCHAR environment variable as the pad character. If this is a space, then yes, it will pad with spaces. If this is Ctrl-@, then it will pad with Ctrl-@ (NUL) characters.