4 Replies Latest reply: May 15, 2014 9:55 AM by Mark Huston RSS

    Excluding numbers from a description field

    dsajec _

      I have a field that can contain the following types of descriptions:

       

      NON-POST    2010

      AUTO INTEREST   

      DEBIT MEMO  3020

      WITHDRAWAL  6070

      DEPOSIT    16813

      MANUAL ACTIVE   

      DEPOSIT     3060

       

      I would like to exclude the numbers from these fields so that I can use a filter on this field.  Any ideas for creating a calculated field that would exclude these numbers without cutting into the alpha characters?

        • Excluding numbers from a description field
          Mike Urbonas

          Yes!  The STRIP function in Monarch V7 handles this easily:

           

          STRIP(DESCRIPTION,"1234567890")

           

          will give you:

           

          NON-POST           

          AUTO INTEREST      

          DEBIT MEMO         

          WITHDRAWAL         

          DEPOSIT            

          MANUAL ACTIVE      

          DEPOSIT            

           

          STRIP is designed to strip out every individual character in the target data (assume the column name is DESCRIPTION).

           

          You will notice there is no need to specify a perfect match when you list the characters you want the STRIP function to strip out.  Just list all possible characters to be stripped out, as I did above, and STRIP does the rest.

           

          Mike Urbonas

          Product Marketing Manager

           

          [size="1"][ November 18, 2003, 09:43 AM: Message edited by: Mike Urbonas ][/size]

          • Excluding numbers from a description field
            Grant Perkins

            As usual Mike's response is spot on and is an example of how V7 offers facilities for greatly simplifying the Template development process and giving elegant solutions.

             

            For example the best I could come up with for V5 was ;-

             

            if(IsAlpha(Right(SORTBY,1))=1,SORTBY,(rsplit(SORTBY,2," ",2)))

             

            or

             

            if(IsAlpha(RSplit(SORTBY,2," ",1))=1,SORTBY,(rsplit(SORTBY,2," ",2)))

             

            Both of these do the same thing in principle acting on a field called SORTBY.

             

            I'm sure someone out there could offer a more elegant expression but nothing that would match using STRIP from V7.

             

            Sometimes progress really is useful!    [img]smile.gif[/img] 

             

            Grant

             

             

            Originally posted by dsajec:

            I have a field that can contain the following types of descriptions:

             

            NON-POST    2010

            AUTO INTEREST   

            DEBIT MEMO  3020

            WITHDRAWAL  6070

            DEPOSIT    16813

            MANUAL ACTIVE   

            DEPOSIT     3060

             

            I would like to exclude the numbers from these fields so that I can use a filter on this field.  Any ideas for creating a calculated field that would exclude these numbers without cutting into the alpha characters? /b[/quote]

            • Excluding numbers from a description field
              Greg Goodman

              New versions take so much fun out of learning workarounds...

               

              Don't know about elegance but sometimes knowing ASCII codes can be real handy -

               

              if(asc(right(SORTBY,1))< 58,RSplitSORTBY,2," ",2),SORTBY)

              • Excluding numbers from a description field
                Mark Huston

                Nice, Greg. I'd have to say that using the ASC() function in this way demonstrates an admirable amount of elegance!

                 

                Happy new year,

                Mark