Patternsearch Non Numeric

[Rikk_2004]

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?

[jamesducker]

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

[Lee Smith]

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, 2007 by Guest
p.s.

[Rikk_2004]

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?

[Lee Smith]

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

A = 1

B = 2

Z = 26

Or something else

Lee

[Rikk_2004]

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.

[Lee Smith]

This works:

Let (

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

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

[mr_vodka]

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;""] )

[mr_vodka]

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

[T-Square]

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

[mr_vodka]

Hi David.

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

[Rikk_2004]

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

[Lee Smith]

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, 2007 by Guest

[T-Square]

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, 2007 by Guest

[Lee Smith]

Hi David,

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

Thank you for posting it.

Lee