converting a pattern of string to spaces in DataStage 11.7

Question

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.

score 1 · Accepted Answer · answered Jun 30 '21 at 21:36

1

How about If Len(Convert("9","",AMT)) = 0 Then "" Else AMT

answered Jun 30 '21 at 21:36

Ray Wurlod

831
1
4
3

Convert("9","",AMT) will replace any occurrence of 9 in a number, but our goal is to convert only if its all 9s – user15906795 Jun 30 '21 at 21:51
1

Read my solution again. Try it. The Convert() function is within the scope of the IF test. – Ray Wurlod Jul 01 '21 at 22:18

converting a pattern of string to spaces in DataStage 11.7

1 Answers1