Page 1 of 1
Posted: Wed Jul 09, 2003 9:26 am
by inter5566
Check the help Docs. for trim
Steve
Posted: Wed Jul 09, 2003 9:37 am
by kduke
rjackson
Add zero if it is always a number.
trim(fieldname,"0","L") - will remove all leading zeros.
oconv(iconv(fieldname,"MD0"),"MD0") - will move decimal place zero places and convert to a number.
Need more.
Kim.
Posted: Wed Jul 09, 2003 10:43 am
by kduke
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.
Posted: Wed Jul 09, 2003 4:00 pm
by ray.wurlod
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