Removing zeros ...

Database
Enthusiast

Removing zeros ...

Hello everyone,

Well I'm using fastexport tool and i've got numbers in my table such as :

0000000000000292.50

because i'm casting char(20).

I'd like to remove leading zeros knowing that the syntax in my fexp is like :

cast(table1.field_number as CHAR(20))

I heard that maybe using the trim function it could work but even if i'm already using a cast ??

Thank you very much for those who will understand my problem and answer to it.
See you.
BR

7 REPLIES
Enthusiast

Re: Removing zeros ...

Well I will help myself and all others who will face the same problem.

To use trim and cast together, the syntax is as follow :

,trim (LEADING '0' FROM(cast(Table1.Field1 as CHAR(20) )))

and the result will be for example 980 instead of 00000000000000000980.

See ya ++
Enthusiast

Re: Removing zeros ...

Finally I still have a problem and I still need help please!

The result I obtain is not only composed of 3 characters '212' but there is a fourth octet symbolised by a square in my text editor.
Here is it : 212

In Hexadecimal, this octet has the value '07'

Is it an octet which symbolize the fact that 17 '0' have been removed ?

Thank you much for help

Enthusiast

Re: Removing zeros ...

Sorry the square doesn't appear in my example. It is 'square212'
Enthusiast

Re: Removing zeros ...

Depending what Teradata version you are using you could simply cast the value as a decimal (that would automatically remove the leading zeros as they serve no purpose) and then simply cast that new value back as text.

The reason I say dependant upon which version is due to the size of the Decmial fields available to you, I think prior to Teradata 12 you can use upto a maximum of Decimal 18, in Teradata 12 you have up to Decimal 36(...ish).

Example syntax (for TD 12+): CAST(CAST FIELDNAME AS DECIAL(19,2)) AS CHAR(20))

Good luck.
Enthusiast

Re: Removing zeros ...

Thank you much for this answer.

Now my result is ok BUT anyway intead of the zeros I have blanks :

;292 ;

and one blank is one octet. So then it increases the weight of my file dangerously.

If I use trim (cast as decimal (18,2) (cast as char (20))

Then ok I don't have the blank after but I still have this undefined octet.

I'm not using TD version 12+.

Thanks in advance
Enthusiast

Re: Removing zeros ...

Reply, my guess would be as the field is definied as a decimal, the single oclet would be a marker for the decimal place.

Rob
Enthusiast

Re: Removing zeros ...

Thank you Rob for answers.

Finally using TRIM (BOTH '0' FROM (CAST Table1.Field1 AS VARCHAR(12))) it works very well !