Find the string between two fields

[qv_testing]

Hi All,

I have two fields - description and search fields, we have to find the value from Search String column

Example  first records 100_QV_BCA_Test and if it is any word available in search string field I have to display (3rd column is my output column)

Description	Search String	OutPut
100_QV_BCA_Test	ABC,BCA,BCC	BCA
101_QS_BCC_Test2	ABC,BCA,BCC	BCC
102_QSD_CCC_Test3	ABC,BCA,BCC	-

Thanks in Advance

[qv_testing]

@marcus_sommer, @Kushal_Chawda - can you please take a look 

[BrunPierre]

Hi @qv_testing, You could do this.

Tmp:
LOAD * ,
SubField(Description,'_') as TmpD,
SubField("Search String",',') as TmpSS
Inline [
Description,Search String,
100_QV_BCA_Test,"ABC,BCA,BCC" 	
101_QS_BCC_Test2,"ABC,BCA,BCC" 	
102_QSD_CCC_Test3,"ABC,BCA,BCC"
];

MapOutput:
Mapping LOAD *
Where not IsNull(OutPut);

LOAD Description,
If(WildMatch(TmpD, '*'&TmpSS&'*'),TmpD,Null()) AS OutPut
Resident Tmp;

Data:
LOAD * ,
ApplyMap('MapOutput',Description,'-') as Output
Resident Tmp;

DROP Table Tmp;

DROP Fields TmpD, TmpSS;

EXIT SCRIPT;

[Kushal_Chawda]

@qv_testing  try below

map_search_string:
mapping LOAD map_from,
'@from'&map_from&'@to' as map_to;
load Distinct trim(SubField(SearchString,',')) as map_from
FROM Table;

Data:
LOAD *,
TextBetween(MapSubString('map_search_string',Description),'@from','@to') as Output
FROM Table;

[MarcoWedel]

If there can only ever be one search string present in description, then this approach might work for you as well:

table1:
LOAD *,
     Evaluate('Pick(WildMatch('''&Description&Chr(39)&','&Chr(39)&'*'&Replace([Search String],',','*'&Chr(39)&','&Chr(39)&'*')&'*'&Chr(39)&')'&','&Chr(39)&Replace([Search String],',',Chr(39)&','&Chr(39))&Chr(39)&')') as OutPut 
Inline [
Description	Search String
100_QV_BCA_Test	ABC,BCA,BCC
101_QS_BCC_Test2	ABC,BCA,BCC
102_QSD_CCC_Test3	ABC,BCA,BCC
103_QSD_ABC_Test4	ABC,BCA,BCC
104_QSD_ABC_BCA_Test5	ABC,BCA,BCC
105_QSD_ABCD_Test6	ABC,BCA,BCC
] (delimiter is '\t');

[MarcoWedel]

or slightly shorter:

Evaluate('Pick(WildMatch('''&Description&Chr(39)&',''*'&Replace([Search String],',','*'',''*')&'*''),'''&Replace([Search String],',',Chr(39)&',''')&Chr(39)&')') as OutPut