|
|
|
Junior Member
 
Group: Forum Members
Last Login: 6/16/2008 11:49:56 AM
Posts: 17,
Visits: 68
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 6/16/2008 11:49:56 AM
Posts: 17,
Visits: 68
|
|
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 ++
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 6/16/2008 11:49:56 AM
Posts: 17,
Visits: 68
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 6/16/2008 11:49:56 AM
Posts: 17,
Visits: 68
|
|
|
Sorry the square doesn't appear in my example. It is 'square212'
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: Yesterday @ 2:48:50 PM
Posts: 24,
Visits: 80
|
|
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.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 6/16/2008 11:49:56 AM
Posts: 17,
Visits: 68
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: Yesterday @ 2:48:50 PM
Posts: 24,
Visits: 80
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 6/16/2008 11:49:56 AM
Posts: 17,
Visits: 68
|
|
Thank you Rob for answers.
Finally using TRIM (BOTH '0' FROM (CAST Table1.Field1 AS VARCHAR(12))) it works very well !
|
|
|
|