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.

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.

I have modified this solution for your scenario:

`CREATE TABLE td3471.Test_x(Delimited_String VARCHAR(100))INSERT INTO td3471.Test_xVALUES ('FL,CHG,MN');INSERT INTO td3471.Test_xVALUES ( 'MN,MN');INSERT INTO td3471.Test_xVALUES( '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_xUNION 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 pWHERE 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...

## 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 bWHERE ','||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).