we need a way to search a string for the first non numeric character in a string. can someone help find the proper method to do that in FMP?

To get the first character that isn't 0-9 I would suggest:

left(

substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(

tableName::fieldName

;"0";"")

;"1";"")

;"2";"")

;"3";"")

;"4";"")

;"5";"")

;"6";"")

;"7";"")

;"8";"")

;"9";"")

;1)

HOWEVER - if by "non-numeric" you mean the first character that is a letter A-Z, it would be neater to do this:

left(filter(tableName::fieldName;"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz");1)

Note that both filter and substitute are case-sensitive.

Best of luck

James

How about:

Left ( Filter ( YourField; "ABCDEFG" ) ; 1 )

Note, the Filter [color:blue] ABCDEFG would need to be expanded to include the whole alpabet, and include the lower case if needed.

HTH

Lee

p.s.

I missed your post, but support your second choice.

Edited July 27, 200718 yr by Guest
p.s.

All the nested substitute function in james's post scare me!

Id like to try the use the left(filter) approach. with that somehow also return the character value# of the string somehow?

I'm not sure what you mean by character value.

A = 1

B = 2

Z = 26

Or something else

Lee

Sort of like pattern count. if the file name is

20070510_record.pdf

it would know that _ is the first non numeric character and it is the 9th character in the string.

This works:

Let (

X = Left ( Filter ( YourField; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz., _" ) ; 1 )

; Position ( YourField ; X ; 1 ; 1 ) )

You do not need to nest your substitutes as such anymore.

You can simply use:

Substitute (field ; [0;""]; [1;""]; [2;""]; [3;""]; [4;""]; [5;""]; [6;""]; [7;""]; [8;""]; [9;""] )

Try something like this.

Let ( x = Left  ( Substitute (field ; [0;""]; [1;""]; [2;""]; [3;""]; [4;""]; [5;""]; [6;""]; [7;""]; [8;""]; [9;""] ); 1); 

        Case ( Length (x); x & " at character position " & Position ( field; x; 1; 1 ) )

      )

*Edit: Didnt see Lee's post

How about a custom function:

FirstNonNum:

If(Pos > Length(value); ""; If(IsEmpty(Filter(Middle(Value; Pos; 1); "0123456789")); Pos; FirstNonNum(Value; Pos + 1)))

This returns a number if a nonnumeric character is found, blank if none is.

David

Hi David.

Yes, a custom function would have been the first choice but he doesnt have advanced. ;)

That looks interesting, and actually we do have advanced now i should update my profile, we recently got it.

Why a Custom Function?

I don't see a need for a CF in this case. This seems like a simple a one time need, and it can be handled with the built in Functions provided.

BTW, I'm not following T-Squared CF, it is unclear to me what goes in the POS, and Value.

Lee

Edited July 27, 200718 yr by Guest

Value is simply the source field; POS tracks where in the field you last looked, and the whole thing recursively runs through the string until a) a nonnumeric character is found, or the end of the string occurs.

Perhaps it would be clearer:

FirstNonNum(Value, Pos)

Case(

  Pos > Length(value); ""; /* End of string reached */ 

  If(IsEmpty(Filter(Middle(Value; Pos; 1); "0123456789")); Pos; /* non-numeric char reached */ 

  FirstNonNum(Value; Pos + 1))/* repeat using next character starting point */

)

David

Edited July 27, 200718 yr by Guest

Hi David,

Yes, the Case Statement, and additional information, did it for me.

Thank you for posting it.

Lee