Check if value is numeric

[El_Pablo]

Hi,

Is there a function that would check if a value is numeric? I need this to check if certain characters in a text field are numbers.

The text field is usually text, but sometime it is numbers followed by the letter "e" (100e, 200e).

My calculation goes as follow:

Let

(

   [

      endE = if (Right (streetName; 1) = "e"; True; False);

      len = Length (streetName) - 1;

      /*Check this line for isNumeric*/

      isNumber = if (isNumeric (left (streetName; len);

      completeStreet = if (endE and isNumber; streetName & " " & roadType; roadType & " " & streetName)

   ];

   completeStreet

)

Why this? In French, we have some streets with numbers which look like this "100e rue" for the "100th street". The rule is that when the street is a number, the road type must precede the street number.

Example

French:

Word street name --> "rue Broadway", "avenue York"

Numeric street name --> "100e rue", "22e avenue"

English:

Word street name --> "Broadway street", "York avenue"

Numeric street name "101th street", "22nd avenue"

Thank for your help

[comment]

You could try:

isNumber = GetAsNumber ( streetName ) ;

That would work for all numbers except zero. Alternatively:

isNumber = not IsEmpty ( Filter ( streetName ; "0123456789" ) )

[El_Pablo]

Thanks,

but is there a real function equivalent to an isNumeric function? That would return true or false? Like in VB

[Raybaudi]

not IsEmpty ( Filter ( streetName ; "0123456789" ) )

returns 1 ( true ) if the streetName contains a number

[El_Pablo]

Thanks, that is what I was looking for. I will modify it to make it a custom function.

It would be nice if in Filemaker 10 (I know it's far) they would integrate regular expression! :laugh:

[El_Pablo]

Here's my function:

isNumeric (value)

Let

(

   [

   trimmedValue = Trim (value);

   filteredValue = Filter ( trimmedValue ; "0123456789.-" );

   lengthOk =    If (Length (trimmedValue) = Length (filteredValue);  True;  False);



   dotOccurence = PatternCount (filteredValue; ".");

   dotOk = If (dotOccurence = 0 or dotOccurence = 1; True; False);



   minusIndex = Position (filteredValue; "-"; 1; 1);

   minusOk = If (minusIndex = 0 or minusIndex = 1; True; False)

   ];

   If (lengthOk and dotOk and minusOk;

      True;

   /*else*/

      False

   )

)

[Raybaudi]

1) trimmedValue isn't needed

2) the number is a number even without "." or "-" so Filter ( value ; "0123456789" ) is enaught

3) dot isn't always a separator of decimal

[comment]

I think you may have missed two significant points:

1. In Filemaker, any number other than zero evaluates as TRUE in a Boolean test;

2. Any text expression containing numeric characters evaluates to a number built of those numeric characters.

Therefore, a test like:

Case ( expression ; result1 ; result2 )

will return result1 when the expression is "abc123", and result2 when the expression is "abc" or "abc0" or "".

---

Your custom function seems to test for a STRICTLY NUMERIC entry. I believe it could be a lot simpler, but as this is unrelated to the original question, and the context is not clear, I will not go into detail.

[El_Pablo]

Indeed, it is not directly related to the original question. But at first, I thought that I would need such a function.

The custom function is used to verify strictly numeric value.

---

To raybaudi:

1) For the custom function, it is necessary if I want to compare the length of the filtered result.

2) Again the purpose of the function is to verify if a value is a number.

3) You're right. Even here in Canada the decimal separator is a coma (,). But it's not difficult to add a decimal separator parameter.

Thanx

[Raybaudi]

017 passes all your tests but isn't a number ;)

[comment]

A lot depends on whether 'value' is text or number. If it's text - should an entry like "1,250" be rejected?

[El_Pablo]

I modified the function so any separator would be accepted. The separator still needs to be a common sense value...

isNumeric (value, separator)

 

Let

(

   [

   decSep = if (separator = ""; "."; separator);



   trimmedValue = Trim (value);

   filteredValue = Filter ( trimmedValue ; "0123456789-" & decSep );

   lengthOk =    If (Length (trimmedValue) = Length (filteredValue);  True;  False);



   dotOccurence = PatternCount (filteredValue; decSep);

   dotOk = If (dotOccurence = 0 or dotOccurence = 1; True; False);



   minusIndex = Position (filteredValue; "-"; 1; 1);

   minusOk = If (minusIndex = 0 or minusIndex = 1; True; False)

   ];

   If (lengthOk and dotOk and minusOk;

      True;

   /*else*/

      False

   )

)

I tried to recreate the isNumeric function from VB 6.

[fabriceN]

how about

filter ( input ; "1234567890-,." ) = input and GetAsNumber ( input ) = input

?

[Raybaudi]

As comment said:

"A lot depends on whether 'value' is text or number"

Try to insert this value into Input ( text field )

0.120

[Raybaudi]

This seems to work better:

input ≥ Floor ( input ) and input ≤ Ceiling ( input )

[El_Pablo]

Ok here is a list of value I tried:

isNumeric ($value)

0.120 --> true

0.221.021 --> false

.021 --> true

00001210.3232 --> true

-9212910.1290109 --> true

-9212910,1290109 --> false

isNumeric ($value, ",")

0.120 --> false

0,221,021 --> false

,021 --> true

00001210,3232 --> true

-9212910.1290109 --> false

[El_Pablo]

You have to check that if "," and "." are in the same value, this would lead to a false result.

0,2121.34 --> must be false

[comment]

Uhm... what I was trying to say is that you need to define the purpose of this test, before you can determine what is a correct result. It makes no sense in abstract.

[El_Pablo]

You are right. I think at this point that we are not anymore answering the inital question. ;)