6 Replies Latest reply: May 10, 2011 9:28 PM by Chris Cammers RSS

    Function like "contain" in the script for multiple substrings



      Does any body have a example or idea for...


      Basically I need to find not a single substring, but search among a list with multiples substrings…


      So that when I would be running script for TABLE 2 I would like to find the codes listed in TABLE 1 in the field called text.


      The codes into de field called text does not follow any pattern.... such as between ( ), " " etc...That's why I need to look individually for all the codes into de field text.


      TABLE 1:








      TABLE 2:

      User      Text

      Alex      Random text /CDD12/ random text

      Rudolf    Another random text *CAD12* random text

      Paul      Other text (DDD25) text

      John      Text 4DD40 random text



      The result expected

      User      Text                                                                     Code

      Alex      Random text /CDD12/ random text                         CDD12

      Rudolf    Another random text *CAD12* random text             CAD12

      Paul      Other text (DDD25) text                                         DDD25

      John      Text 4DD40 random text                                       4DD40



      I thought about these functions (FindOneOf or  substringcount ) but I not so sure..









      Any ideas or examples?



        • Function like "contain" in the script for multiple substrings

          Give this a shot....


          mid(Text,index(Text,'CDD'),5) as 'Code'


          That's only gonna work if all of the codes are CDD## which the 5 you posted are so maybe that will be fine.

            • Function like "contain" in the script for multiple substrings

              I really appreciate your help, but the Code table does not have a pattern also..









              So the code does not always begin with CDD nor necessarily have 5 digits.


              Do you have any other idea?


              Someone else has?



                • Function like "contain" in the script for multiple substrings

                  Well in the example you just posted again all of the codes start with CDD and have 2 numbers so... show me one that is different b/c I think one of us is misunderstanding and I'm not sure who that is yet.

                    • Function like "contain" in the script for multiple substrings

                      I'm really sorry for the example given.


                      I have just edited the original topic

                      I think now it would be easier to understand what I meant wiht Code table does not have a pattern also..



                        • Function like "contain" in the script for multiple substrings

                          Yeah that makes it a lot harder...


                          I have to figure there is some way to do this with peek and a for loop but that could be a nightmare if you have a LOT of data.  Maybe try something like this...


                          For i = 1 to  NoOfRows('Table1')


                               LET vCode=peek('Table1', 1-$(i),'Code');





                          $(vCode) as 'Code'

                          INLINE [

                          inline stuff goes here


                          WHERE index(Text,$(vCode))>0;


                          Next i

                            • Function like "contain" in the script for multiple substrings
                              Chris Cammers

                              I think your best bet take a couple of steps, what you'll want to do is load all the strings into a single variable and then use the wildmatch() function to figure out what string has the match. So I'll try to modify your example to illustrate.


                              //Load the codes into your table adding a record number and asterics on the code so you can use the

                              //wildmatch function later


                              Load * Inline [

                              Record, Code

                              1, *CAD12*

                              2, *DDD25*

                              3, *B6D60*

                              4, *CDD12*

                              5, *4DD40*



                              //Now in a temporary table you can concatenate the codes enclosed in single quotes and delimited with //commas




                              Concat(chr(39) & Code & chr(39), ',') as Codes

                              Resident TABLE1;


                              //Store the concatenated code list into a variable

                              Let vCodeList = Peek('Codes',0,'CodesTemp');


                              //The wildmatch function returns a number indicating the position of the matching list item 





                                  WildMatch(Text,$(vCodeList)) as Record;

                              Load * Inline [

                                  User, Text

                                  Alex, Random text /CDD12/ random text

                                  Rudolf, Another random text *CAD12* random text

                                  Paul, Other text (DDD25) text

                                  John, Text 4DD40 random text



                              //Finally join the table and clean up.   

                              Left Join (TABLE2)

                              Load * Resident TABLE1;


                              //Clean up

                              Drop Tables TABLE1, CodesTemp;