I have two tables for example take two small input tables:-

Table 1
Desc                                           PID     CID
BNF BO PMT WFT-100023                          10048  
BNF BO PMT WFT-100034                          10048 
CR.IN.RATE   , 0005                                     10054
CR.IN.RATE   , 0000                                     10054
BNF BO 000005967 WBG_TRA035IN0216868           100001
BNF BO 000005947 WBG_TRA034IN0216834           100001
BNF BO 000005131 ATT_09003806STOCK             100002
BNF BO 000003431 ATT_09003456STOCK             100002

Table 2
Desc                                     
BNF BO 000004131 ATT_0900355STOCK              
BNF BO 000006457 WBG_TRA035IN0345663     
CR.IN.RATE   , 0056677                   
BNF BO PMT WFT-1000456                   
BNF BO 000005126 MRG328766PARKING
BNF BO 000005127 PNEUMATICS143572DW23

in table2 desc has to find in table1 desc which is most similar words has to pick and map the PID or CID

 BNF BO 000005131 ATT_09003806STOCK - Key word is ATT
 BNF BO 000006457 WBG_TRA035IN0345663 - KEy word is WBG
 CR.IN.RATE   , 0056677               - CR.IN.RATE

My output should be like like this :

Table2
Desc                                     ID
BNF BO 000004131 ATT_0900355STOCK        100002
BNF BO 000006457 WBG_TRA035IN0345663     100001
CR.IN.RATE   , 0056677                   10054
BNF BO PMT WFT-1000456                   10048
BNF BO 000005126 MRG328766PARKING
BNF BO 000005127 PNEUMATICS143572DW23

I can't hardcode or specify rule because descriptions vary but need to find ID's by desc.. have an option to do in java or postgresql..

I tried to use regexp but can't find a way..

Can someone please suggest a good approach..

0 Answers