How to remove the padding charachers from a string

Billyqing · Post by **Billyqing** » Fri May 15, 2009 9:01 am

Does any one know how to remove the padding charactors like 0x0 from a string?

Thanks in advance

priyadarshikunal · Post by **priyadarshikunal** » Fri May 15, 2009 11:42 am

If coming from source, Trim is what you need. Else check Padchar in environment variables.

Billyqing · Post by **Billyqing** » Fri May 15, 2009 2:11 pm

I have tried all TRIM functions but none of them works.

chulett · Post by **chulett** » Fri May 15, 2009 2:19 pm

It probably works fine but then they get added right back on. How about more details about what exactly you are doing where and what the end result is you are trying to achieve?

ray.wurlod · Post by **ray.wurlod** » Fri May 15, 2009 4:47 pm

In particular, what data types are involved?

ArndW · Post by **ArndW** » Sat May 16, 2009 5:52 am

Remember that CHAR fields aren't trimmed, a Char(5) string with "hi " is still "hi " after TRIMming.

chulett · Post by **chulett** » Sat May 16, 2009 7:06 am

Remember that without code tags the forum software removes all of that nasty extra white-space you accidentally put in there.

ray.wurlod · Post by **ray.wurlod** » Sat May 16, 2009 6:13 pm

There are ways of getting "hi___" to work without code tags, if you really want to.

chulett · Post by **chulett** » Sat May 16, 2009 8:34 pm

I know, it was just a friendly poke in the kidneys for Petty Officer Wussing.

ArndW · Post by **ArndW** » Sun May 17, 2009 6:10 am

I stand corrected and am appropriately

Code: Select all

"chastised     "

parag.s.27 · Post by **parag.s.27** » Mon May 18, 2009 5:41 am

chulett wrote:It probably works fine but then they get added right back on. How about more details about what exactly you are doing where and what the end result is you are trying to achieve? ...

We had similar problems in our case where a Char(0) characted is getting appended at the end of the string. Even though the Trim function is applied in the source extraction query or the transformer after that then also it is not getting removed.

Then we tried a weird solution. Initially extract the data and put it in a Dataset. Then we read the dataset and applied function

Code: Select all

Trim(<Col name>,Char(0),'A')

. This resolved our problem and that special character never returned in the data.

Billyqing · Post by **Billyqing** » Tue May 19, 2009 2:01 pm

Hi Everyone,

Thanks for all valuable inputs.
This issue has been solved now.

The details that we did here are:

The source data field is Varchar. The target file we produced is defined as Char field. The Char(0) charactor was padded in the ETL processes because the Environment Variable used for padding defaults as Char(0). I had tried to use Trim function but it would not work. All we did is to change Char fields to Varchar in the ETL processes. It works now but spenting a lot of time.

Code:
Trim(<Col name>,Char(0),'A')

This code is very good. I will try to use this if the problem comes again.