4 Replies Latest reply on Jul 12, 2018 3:35 PM by Stephen Hicks

    Is a Number Logic

    Jerry Ward

      Hi I have a field which can be letters or numbers and I need to run logic where if the field (Letter Code) "Is a number" then YES otherwise NO.  In Excel this is usually done by ISNUMBER().  Any Ideas?

       

      IF [Class Code] = 'P' OR [Class Code] = 'S'  AND FLOAT([Letter Code]) >= 0

      THEN 'YES' ELSE ' ' END

       

      Thanks.

        • 1. Re: Is a Number Logic
          Joe Oppelt

          there is the INT() function.

           

          if ISNULL(INT([string field])) THEN (you have something other than numeric chars in string_field) END

           

          INT() will return a numeric value if it can evaluate the string.  Otherwise it will return NULL.  So if ISNULL() evaluates to true, then you had something more than numeric characters in string field].

          • 2. Re: Is a Number Logic
            Joe Oppelt

            And probably, if you are looking to operate only on numeric strings, you would want to do:

             

            IF NOT ISNULL(INT([string field])) ...

            • 3. Re: Is a Number Logic
              Carl Slifer

              Howdy Jerry,

               

              Can we attempt the following calculated field?

              IIF(ISNULL(INT([Field])),'No','Yes')

               

              Firstly it converts your field to an integer. Anything that's all numbers can do this. Anything with any strings cannot.

              Then we check to see if it is null, we return either true or false for this part. True are those that couldn't be converted.

              Finally we use the IIF function which checks to see the first expression returned true or false and then assigns 'no' or 'yes' depending on our result.

               

              Cheers!

              Carl Slifer

              InterWorks

              • 4. Re: Is a Number Logic
                Stephen Hicks

                Here you go!

                 

                LEN(REGEXP_REPLACE([Seat From Num],'^[0-9]*$',''))=0

                 

                null = null

                False = Alphanumeric

                True = Numeric

                 

                Works by removing all numbers to see if you end up with nothing left