DSXchange

Hi all,
i want to remove additional zeros from records using a transformer. I have tried with Trim function but it is not working .Also, please give the format in which this has to be done.

Whats your datatype for which you are trying to remove leading zeros?

If it decimal, then after removal, you will have to carry it as char or varchar or int. Because, in PX decimal are always have leading zeros when they are stored in datasets or carried through links (i.e. virtual datasets).

If your input is decimal and then use DecimalToString(InCol, 'supress zero')

If the in col is string, then a normal trim should work.

May be some special handling for first char of the col, will be needed as it will be sign char.

How precisely have you specified your Trim() function?

Please give more details about the problem, like what is the input datatype, what is output datatype etc. Because there are many ways you can handle it.

ray.wurlod wrote:How precisely have you specified your Trim() function? ...

Hi,
I have tried with Trim function Trim("columnname","0","L") but this is not working.The data type is Char in input and output.Let me know if there is any other format which i need to give.With the above function used I am getting junked data in the output.Also i have tried various other formats of trim.

Hi,

As far as I know Trim function works for Varchar data type. You cannot use Trim for fields which are of Char data type.

Please correct me if I am wrong.

As I am from server bkg, my understaning is it works for all.

I creates a job with char (5) having leading zeros and then tried trim on it.

It is giving proper results i.e. 00005 is converted to "5 " value after using trim.

So I feel there is no such restriction.

Lucky wrote:...As far as I know Trim function works for Varchar data type. You cannot use Trim for fields which are of Char data type...

You can use the TRIM() function on CHAR, but since the resultant output if of fixed length it will automatically pad the output according to string rules. You can use it to replace/remove zeroes.,

Using the trim function as you have mentioned
Trim("columnname","0","L")
would only remove the leading zeros from the "columnname" specified.

You need to be clear if you would want to remove leading 0 or trailing 0 or anything in betn as well..

Using option "A" Removes all occurrences of character
"B" Removes leading and trailing occurrences of character.
"T" Removes trailing occurrences of character.

Hope this helps

The OP has begun a new thread here on this topic.

This will do it:

DecimalToString(ColumnName),"fix_zero,suppress_zero")


NOTE: One of the previous posters spelled suppress with one p.

Lucky wrote:Hi,

As far as I know Trim function works for Varchar data type. You cannot use Trim for fields which are of Char data type.

Please correct me if I am wrong.

I am not sure, but I will say that I was experiencing a challenge with a Lookup Stage as my input was Char and lookup reference was VarChar. Once I converted Char to VarChar (Transform Stage) it worked.

With a Modify stage it might work even faster. You will still need a string_trim function, however. Refer to Orchestrate Operators guide for more information, as they seem to have left this function out of the Parallel Job Developer's Guide. This document may also prove useful.