Scenario related to String Manipulation with JOIN

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Scenario related to String Manipulation with JOIN

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

DARORA

Accepted Solutions
Enthusiast

Re: Scenario related to String Manipulation with JOIN

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;

DARORA
1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: Scenario related to String Manipulation with JOIN

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.

Enthusiast

Re: Scenario related to String Manipulation with JOIN

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;

DARORA