joining two tables using SUBSTR

Database
Enthusiast

joining two tables using SUBSTR

Hi All,

I have a requirement where there are two tables Tbl1 and Tbl2 .i want to join these two tables based on the columns Cntry_code.

The Data is like :

Tbl1                                                          Tbl2 

Column Name : Cntry_code                      Column Name : Cntry_code

Values :  'FL'                                              Values :  'FL,CHG,MN'

               'CHG'                                                          'MN,MN'

               'DET'                                                           'DET'

               'MN'

My requirement,  is I need to join Tbl1.'FL' to Tbl2.'FL' , Tbl1.'CHG' to Tbl2.'CHG' ,but that is present in the 1st row as a substring.

Also if the same code 'MN,MN' occures twice , it should take only one 'MN' and join.

Can anyone please help me in this? 

Thank you in advance!!

4 REPLIES
Enthusiast

Re: joining two tables using SUBSTR

Hi,

The following thread will help you to construct a solution, you can get and insert these values into a temp table and then join on the basis of these values.

http://forums.teradata.com/forum/enterprise/split-delimited-column-into-separate-rows

I have modified this solution for your scenario:

CREATE TABLE td3471.Test_x
(
Delimited_String VARCHAR(100)
)

INSERT INTO td3471.Test_x
VALUES ('FL,CHG,MN');
INSERT INTO td3471.Test_x
VALUES ( 'MN,MN');
INSERT INTO td3471.Test_x
VALUES( 'DET');

WITH RECURSIVE parse_list (response_key, delim_pos, item_num, element, remainder) AS
(
SELECT
SUBSTRING( Delimited_String FROM 1 FOR POSITION(',' IN Delimited_String)) AS response_key
,0, 0, CAST(response_key AS VARCHAR(100)) AS element
,SUBSTRING(Delimited_String FROM POSITION(',' IN Delimited_String)+1 FOR CHAR_LENGTH(Delimited_String)) AS remainder
FROM
TD3471.Test_x
UNION ALL
SELECT response_key,
CASE WHEN POSITION(',' IN remainder) > 0
THEN POSITION(',' IN remainder)
ELSE CHARACTER_LENGTH(remainder) END dpos,
item_num + 1,
TRIM(BOTH ',' FROM SUBSTR(remainder, 0, dpos+1)),
TRIM(SUBSTR(remainder, dpos+1))
FROM parse_list
WHERE dpos > 0
)
SELECT DISTINCT TRIM(BOTH ',' FROM element)
FROM parse_list p
WHERE element NOT LIKE ''
ORDER BY response_key, item_num;
Khurram
Enthusiast

Re: joining two tables using SUBSTR

Thank you Saeed Khurram..The code is working...

Teradata Employee

Re: joining two tables using SUBSTR

Try this one:

sel * from tb1 a , tb2 b

where b.cntry_code like '%'||trim(a.cntry_code)||'%' ;

You will get these pairs:

a.cntry_code    b.cntry_code

FL                      FL,CHG,MN

DET                   DET

CHG                  FL,CHG,MN

MN                    FL,CHG,MN

MN                    MN,MN

Regards

Junior Contributor

Re: joining two tables using SUBSTR

This will also return a row for 'CH' matching 'CHG', you have to change it slightly:

SEL * FROM tb1 a , tb2 b
WHERE ','||b.cntry_code||',' LIKE '%,'||TRIM(a.cntry_code)||',%' ;

And it will cause a product join (unless there's an additional join-condition using equality).

This string-splitting topic has been done multiple times, depending on the release it might be quite simple (STRTOK_SPLIT_TO_TABLE in TD14).