3 Replies Latest reply: May 15, 2014 10:09 AM by Grant Perkins RSS

    Filtering out text

    Marc Osborne



      I would like to create a filter that shows only shows records where a certain field doesn't start with a certain character. If I were to write out the filter definition it would be: Show only those records where the field SKU doesn't begin with a lowercase letter p[/I]


      How would I do this? Casting the net a little wider, what functions allow you to filter through the inclusion or exclusion of strings within a field?





        • Filtering out text
          Joe Berry

          In this case, since it is the first letter of the SKU, you can create a filter using the function Left as follow:


            • Filtering out text
              Marc Osborne

              So simple, elegant, and effective! Thank you very much.

                • Filtering out text
                  Grant Perkins



                  A quick clarification here.


                  Might some of the records you DO want start with UPPERCASE P? If so you may need a slight adaptation to Joe's suggestion.


                  In filters Monarch is Case Insensitive so p will include P. (Note that for TEMPLATE TRAPS you can choose whether they are to be case sensitive.)


                  If you really need case sensitivity in the filter you will likely need to use the ASC() function and provide the ASCii value for lowercase p.  The Help example for the ASC function provides a sample formula if you need it. ASCii.txt for obtaining the ASCii number you require can be downloaded from the Datawatch web site [URL="http://www.datawatch.com/downloads/ascii-chart.txt"]here[/URL].