Check the help Docs. for trim
Steve
Removing Leading Zeroes
Moderators: chulett, rschirm, roy
rjackson
oconv(iconv(fieldname,"MD0"),"MD0") will only work if you have no decimal places because it will trucate everything after the decimal place.
Almost everything in DataStage is a string.
X = 123 if X = "00123" + 0.
That is always the easiest way. oconv() and iconv() were designed originally to clean up this kind of problem but they are relatively slow because they can do so many things. You can always trap errors like this:
if num(X) then X + 0 else X
Kim.
oconv(iconv(fieldname,"MD0"),"MD0") will only work if you have no decimal places because it will trucate everything after the decimal place.
Almost everything in DataStage is a string.
X = 123 if X = "00123" + 0.
That is always the easiest way. oconv() and iconv() were designed originally to clean up this kind of problem but they are relatively slow because they can do so many things. You can always trap errors like this:
if num(X) then X + 0 else X
Kim.
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Kim's answer covers almost everything I wanted to say.
You may need to consider "" (the zero length string) and NULL (the unknown value).
NULL isn't a problem, since if the result of the condition test is NULL in an IF statement, the ELSE path is always taken (the rationale is that we can't assert that an unknown value is true).
However, "" is considered to be numeric in a numeric context, so that the expression would replace "" with 0. If you know you're never going to get "" in your data, you don't need to worry about this. Otherwise modify the expression as follows.
If Len(x) And Num(x) Then x + 0 Else x
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
You may need to consider "" (the zero length string) and NULL (the unknown value).
NULL isn't a problem, since if the result of the condition test is NULL in an IF statement, the ELSE path is always taken (the rationale is that we can't assert that an unknown value is true).
However, "" is considered to be numeric in a numeric context, so that the expression would replace "" with 0. If you know you're never going to get "" in your data, you don't need to worry about this. Otherwise modify the expression as follows.
If Len(x) And Num(x) Then x + 0 Else x
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518