i need to convert a pattern of digits in an amount to spaces. like if i have all 9s then that should be converted to '', but if 9 is part of a number then it should not convert. For eg: 9, 99, 99.99, 9.999, 999.9..etc these should be converted to '', but if the amount is 90, 119, 291, 889, 100.99, 999.11 then it should not convert. CONVERT() is not working, so i tried to COUNT(AMT,9)=LEN(AMT). I think this won't work as LEN() will count DOT in the decimal posItion. So count (9.99, 9) would be 3 but LEN(9.99) would be 4.
My current code in DataStage 11.7 has IF CONVERT('9','', AMT) ='' THEN 0 ELSE AMT
Please help me with solution.
How about If Len(Convert("9","",AMT)) = 0 Then "" Else AMT