Scenario related to String Manipulation with JOIN

[DeepakArora]

Hi All,

I came across one real time scenario. I have 2 source tables as

Table1 :

COL1
MUMBAI
KOLKATA
DELHI

Table2:

COL1                                 COL2

'HALKA MUMBAI'              30

'KOLKATA NIGHT'             20

'SUN DELHI RISES'      10

Expected Output : (Join the tables if strings are matching  either partially match).

COL1                 COL2
MUMBAI            30
KOLKATA          20
DELHI                10

I tried to find one solution approach, but its not working for ('SUN DELHI RISES' = 'DELHI')

SELECT A.COL1,PRINCE
FROM
FINANCIAL.TEAM A
INNER JOIN
FINANCIAL.TEAM_INFO B
ON
A.COL1= TRIM(REGEXP_REPLACE(B.COL1,REGEXP_REPLACE(B.COL1,
A.COL1,
'',
1,
0, 
'i' 
),'', 
1, 
0, 
'i'
));

Please suggest any generic solution that will work for all cases.

Thanks,

Deepak Arora

[Fred]

Depends on what is considered a "partial match".

If you only want to check that the string in Table1.Col1 is contained within Table2.Col1, then something like POSITION(Table1.Col1 IN Table2.Col1) > 0 would satisfy your example.

[DeepakArora · Accepted solution]

Thanks Fred. 

Its working perfectly.

SELECT A.COL1,B.PRINCE
FROM
TEAM A
INNER JOIN
TEAM_INFO B
ON
POSITION(A.COL1 IN B.COL1)>0;