4 Replies Latest reply: May 15, 2014 10:03 AM by Tastebuds _ RSS

    Extracting a string from a field

    Diane Miller

      I am trying to extract a string from a field but when I use "Extract(,,)" instead of getting the string I am looking for, I get everything after the string (if there is anything).


      The field I am working with is and some examples of what might be included in the field are:

        No Signed CFT

        CFT Not Available

        Pd Prior to Purchase

        CFT Missing

        Per Client 100% Charity Adjustment


      What I want out of the field is "CFT" when the field contains the string "CFT" (regardless of case). I would prefer to create a Calculated Field but would also like to filter on the same thing.


      I am sure this is possible, I have found very little Monarch can't do but I can't find it. I am on Monarch Pro V 8.01


      Thanks for the help.

        • Extracting a string from a field
          Data Kruncher

          Hi Diane,


          Create a character type calculated field, using this expression:

          [SIZE=2]if(instr("CFT",[SBA Reason])>0,"CFT","")[/SIZE][/CODE]


          If the string "CFT" is contained within SBA Reason, the instr function will return a value greater than zero, equal to the position within SBA Reason where CFT appears. If it doesn't exist, you get an empty string.


          You can then filter where the value of this field is "CFT".




            • Extracting a string from a field
              Grant Perkins

              Just a couple of quick observations for future finders of this thread ...


              If filtering without the desire for a calculated field as an interim output Kruncher's formula could be embedded in a filter formula.


              For anyone using V9 or above there is an option to use the TEXTLINE function in SEARCH mode to identify the existence of the string being searched for. The result would return the entire line (in this case the entire field) so to make a calculated field one would either need to process the resulting string and slice and dice OR use an IF statement to populate the field with something suitable.