Discard trailing spaces in char field

abhilashnair · Post by **abhilashnair** » Tue Apr 12, 2011 6:36 am

Source DB2(ODBC Enterprise)---->Transformer------->Target SQL Sever(ODBC Enterprise)

The incoming field is Char(3). Target also it is Char(3)
Spaces are being added to end while writing to target. I used Trim in transformer. No change. I tried Convert(). No effect. Any suggestion. Ineed only actual value in target without trailing space

APT_STRING_PADCHAR is set to 0x0 at project level

chulett · Post by **chulett** » Tue Apr 12, 2011 6:39 am

It's a char field... that's how they work. Everything is fine.

abhilashnair · Post by **abhilashnair** » Tue Apr 12, 2011 7:00 am

But as per requirement, need to do away with those trailing spaces...If I define target metadata as varchar, it works..Is this a good idea? Is there any other way?

chulett · Post by **chulett** » Tue Apr 12, 2011 7:06 am

Your requirements are bad for a CHAR. If your database target was a varchar, then yes you could 'discard trailing spaces' easily.

abhilashnair · Post by **abhilashnair** » Tue Apr 12, 2011 7:17 am

chulett wrote:Your requirements are bad for a CHAR. If your database target was a varchar, then yes you could 'discard trailing spaces' easily. ...

The problem is that the downstream apps which use the sql server target table are facing issues while querying. The field is Char in target as well, but DataStage is appending null at the end which is being picked up as a special character in sql server. I think if I use Varchar the issue will be resolved. Do I need to change APT_STRING_PADCHAR value...currently it is 0x0

chulett · Post by **chulett** » Tue Apr 12, 2011 7:32 am

Yes, you need to change APT_STRING_PADCHAR to a space: 0x20