5 Replies Latest reply: Nov 18, 2015 4:39 AM by Boris Adloff RSS

    Comparing Text Strings

    Drew Collins



      As part of our data checks, I have a table like below:


      Material BoughtMaterial Used
      Brick; Stone; MortarBrick
      Stone; RockRock; Wall Paper
      Brick, PebblesStone


      I need to ensure that what is in column B is also in column A.


      In the above example, row 1 is fine, but rows 2 and 3 are an issue. How can I compare these two fields for "likeness" without writing a bunch of if statements?


      Thanks in advance!

        • Re: Comparing Text Strings
          Anand Chouhan



          But in second you have also Rock string do you find the first or second position of the string or it just presence of the strings in the field.




          • Re: Comparing Text Strings
            Clever Anjos

            Do you want a "one-to-one" comparison?

            Could you describe better your requirement, since "Brick" appears on first column, but what if have more values?

              • Re: Comparing Text Strings
                Drew Collins

                I would essentially like to write something like below:


                IF([Material Used] like [Material Bought],'Okay','Not Okay')


                and this would evaluate the two to see if the string from Material Used is contained within Material Bought. I want to avoid having to write if statements containing all the possible values.

              • Re: Comparing Text Strings

                Hi Drew,


                if I get you right, you have defined items, separated by a semicolon?
                or is it undefined strings you want to compare like

                Material BoughtMaterial Used
                BrickStone; MortarBricks


                That would make a different story, guess you would have to run loops on that. Ghastly.



                As long as you can split up the items using a defined separator (";") as given in your sample, you may want to extract data (using the subfield command) into two tables, one for column 1 and one for the other, each with an id for the line and a field for the text.


                The data would look like this:

                Original table:

                Brick; MortarBrick
                StoneSand; Mortar















                Then, grouped per line_id, you count matches of both fields,
                here resulting in a sum of 1 for line_id 1 and 0 for line_id 2.


                Any line with >0 matches is what you are looking for.




                Would that help?