2 Replies Latest reply: May 15, 2014 9:54 AM by Steve Caiels RSS

    detecting embedded numbers?

    drpepper _

      I had a problem when dealing with Post Codes which required me to detect the presence of any number in the field. I had a little trouble trying to build the expression and I am convinced I was missing something blindingly obvious.

       

      As it happens, I have managed to get around having to do this but for my sanity's sake can someone please let me know how this is achieved.

       

      e.g field1 is ABCDEF77GGG

      I did think of something along the lines of

      instr(,field1) but obviously the square brackets do not work as ranges in Monarch

       

      Thanks!

        • detecting embedded numbers?
          Grant Perkins

          If you have V7 or later you could consider using the STRIP function to remove all possible characters except numbers and do an ISEMPTY() or LEN()>0 check on what is left. If the result is not empty or has length then there are characters present which, presumably, are numbers.

           

          The jury would be out on whether it is elegant but it would be effective.

           

          Alternatively a nested IF statement checking for a numeric character somewhere in the string would do the trick.

           

          [font="courier"]if(instr("1",[Test Field])>0, "NUMERIC",

          if(instr("2",[Test Field])>0, "NUMERIC",

          if(instr("3",[Test Field])>0, "NUMERIC",

          if(instr("4",[Test Field])>0, "NUMERIC",

          if(instr("5",[Test Field])>0, "NUMERIC",

          if(instr("6",[Test Field])>0, "NUMERIC",

          if(instr("7",[Test Field])>0, "NUMERIC",

          if(instr("8",[Test Field])>0, "NUMERIC",

          if(instr("9",[Test Field])>0, "NUMERIC",

          if(instr("0",[Test Field])>0, "NUMERIC",

          " " ))))))))))  /font[/quote]This will test for a numeric character somewhere in a field and fills the calculated field with NUMERIC if a number exists or leaves it blank if no numeric character is present.

           

          There must be other ways but these are the first to come to mind.

          Hope this helps.

           

          Grant

          • detecting embedded numbers?
            Steve Caiels

            Hi,

             

            If you are trying to filter all records that have a numeric in the postcode filed, then I agree with Grant.  The filter expression would be

             

            Len(postcode)<>len(strip(postcode,”1234567890”)

             

            But to split the field, you’d need two expressions.  To get the bit before the number, you could try

             

            [font="courier"]if(.not.isalpha(substr(postcode,2,1)),left(postcode,1),

            if(.not.isalpha(substr(postcode,3,1)), left(postcode,2),

            if(.not.isalpha(substr(postcode,4,1)), left(postcode,3),

            if(.not.isalpha(substr(postcode,5,1)), left(postcode,4), if(.not.isalpha(substr(postcode,6,1)), left( postcode,5),

            if(.not.isalpha(substr(postcode,7,1)), left(postcode,6),

            postcode))))))[/font][/quote]Regards

            Steve