Function like "contain" in the script for multiple substrings

[#1]

Hi!

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:

Code

CAD12

DDD25

B6D60

CDD12

4DD40

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..

Table:

LOAD

     [user],

     FindOneOf([Text],[Code],0)

    substringcount([Text],

)

Any ideas or examples?

THANKS!

[#2]

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.

[#3]

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

Code

CDD12

CDD25

CDD60

CDD12

CDD40

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

Do you have any other idea?

Someone else has?

Thanks

[#4]

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.

[#5]

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..

Thanks!

[#6]

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');

LOAD

User,

Text,

$(vCode) as 'Code'

INLINE [

inline stuff goes here

]

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

Next i

[chriscammers]

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

TABLE1:

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

CodesTemp:

Load

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 

TABLE2:

Load

    User,

    Text,

    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;