AlNum Function Giving Me Headaches

[Tampa_Indy]

I am trying to run the following:

if alNum(left(trim(<string>),1)=1 and length(trim(<string>)) > 1 then SetNull() Else <string>[2,10]

However, it is not working?! Any idea what I am doing incorrectly? I removed everything except AlNum(<string>)=1 then setnull() else <string>[2,10] but that does not appear to work either??

[chulett]

Why don't we start by you spelling out in words what it is you are trying to accomplish, your requirements here.

[Tampa_Indy]

I am trying to distinguish non-numeric values with alphanumeric ones. For example, if you have the following values:

#
^345
* 999
STRK
STLM
Close*tome

I would want to completely remove '#', have a field contain '345' and ' 999' and a different field with 'STRK' 'STLM' and 'Close*tome'

[chulett]

So... if the first character is alphanumeric then do something else do something else? And not at all clear on what you mean by "a different field".

What about your expression "is not working"? To me it looks like you want to set the result to NULL if the first character is alphanumeric and there is more than one character in the string, otherwise the result is everything minus the first character.

[priyadarshikunal]

as per the expression you posted if the first character of the string is alpha numeric and the string is more than one character long then setnull() else take last 9 characters. which doesn't sound same as the example you provided. Can you be more clear on requirement?