Jump to content
Sign in to follow this  
Rikk_2004

Patternsearch Non Numeric

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

A = 1

B = 2

Z = 26

Or something else

Lee

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

This works:

Let (

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

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Hi David.

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Hi David,

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

Thank you for posting it.

Lee

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.