Ok... if you are now saying that you only want to remove the 'X' from the field if it is the last character of the string, then you are right - you can't use a simple Convert function to do that.
You'll need to do something like Ray posted, check the last character and if it is an 'X' then extract everything up to (but not including) the last character, otherwise pass the entire field. Or count the number of 'X' characters and use EReplace to remove just the 'last' occurance. Whatever is appropriate.
-craig
"You can never have too many knives" -- Logan Nine Fingers
This is insane. This is like giving you a timestamp 04/05/06 and tell you to determine if its MM/DD/YY or DD/MM/YY. Next to impossible.
If you can assure that the 'A' only shows up at the end of numeric values then you can use DIGITS() transform and get done with it by testing the first value and doing a ISNUM check on it.
Some generic rules need to be layed out.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Ok i am glad there is a rule defined. Go for Ray's solution. See if it passes out all your tests. It should, Ray's solutions are pretty much all inclusive.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Well then work out a match pattern that will work for you. My first option would handle PROCTOR & GAMBLE - G&A properly, as there's no numeric character (to match the "1N" part of the pattern). Adapt.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.