Suppression Of left zeroes

[mpouet]

Hi,

I need to suppress the left zeroes in a string.
For example : '0000525600254000' ==> '525600254000'
Does anyone have an idea ?

Thanks
Matthieu

[ucf007]

You can use :
Right(mystring,Len(mystring)-4)

if you hav always 4 zeros at the beginning...

[pnchowdary]

Hi,

You can use the below transformation.

Code: Select all

FMT(InLink.InCol,"16LZ")

Where 16 is the total size of the column

[ashwin2912]

BWT, FMT is a server function..you would need a basic transformer to use that.

In a normal (parallel) transformer:
Just put the string into a Bigint field by using foll. transformation:

Code: Select all

StringToDecimal("0000525600254000")

Code: Select all

output will be: 525600254000

All your leading zeroes will be removed.

Just not sure how much a Bigint can store. I tried putting 16 9's into it. It works. So in your case it shouldnt be a problem as your string length is 16.

Can anyone throw light on exactly what are the max limits that can go into:
TinyInt
SmallInt
Integer
BigInt

[mpouet]

Thanks all,

ucf007 : the number of left zeroes is variable. It would be too simple with a fixed number !
pnchowdary : FMT is a server fonction and the basic transformer is not available in version 6.
ashwin2912 : I thought of this solution, but I don't like it much. If I don't find another one I'll do this.

Thanks a lot
Matthieu

[makreddy]

Use the Convert function. replavce 0's with no spave i.e. ''.


Thanks
aravind Reddy

[ray.wurlod]

There is an undocumented flag [suppress zero] available in the Modify stage. Read about it here.

[mpouet]

Thanks Ray, but the Modify stage is not available in version 6 of DataStage PX...

Matthieu

[srikanthd1978]

..here are the limitations on the int fields( except SMALLINT )..

BIGINT : 64 bit - 8 byte . This is an 8 byte integer with a precision of 19 digits. The range being -9223372036854775808 to + 9223372036854775808

INTEGER: 32 bit - 4 byte. This is a 4 byte integer with a precision of 10 digits. The Range being -2147483648 to +2147483647

SMALLINT: 16 bit - 2 byte. This is a 2 byte integer with a precision of 5 digits. The Range being -32768 to +32768

..correct me if i am wrong..

[ray.wurlod]

You left out TinyInt and all the unsigned variants.

TinyInt is one byte (eight bits) long. Signed it can range between -128 and +127. Unsigned it can range between 0 and 255 (2^8-1).

You can generalise to the remaining unsigned types from that. 0 through 65,535 (2^16-1) for SmallInt, 0 through 4,294,967,295 (2^32-1) for Integer, 0 through 18,446,744,073,709,551,615 (2^64-1) for BigInt.

[subasht]

If your intention is to write this formatted string into a flat file, the out_format option for the column can be used. You will have all the formatting options form the C sprintf command available. The option "%16i" should do for this one